Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Here is the cenario that I need help:
I have 2 tables.
Table-1: WorkItems. This table has all latest Workitems from specific project with isCurrent = TRUE for all records.
WorItemId | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork | IsCurrent |
40001 | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | TRUE | ||
40002 | Task2 | Task | Active | 30001 | Project1 | Prabhat | 6 | 2 | 4 | TRUE | ||
40003 | Task3 | Task | Closed | 30001 | Project1 | Rupak | 7 | 7 | 0 | TRUE | ||
40004 | Task4 | Task | Active | 30002 | Project1 | Alok | 2 | 1 | 1 | TRUE | ||
40005 | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | TRUE | ||
40006 | Task6 | Task | Closed | 30003 | Project1 | Prabhat | 4 | 4 | 0 | TRUE | ||
40007 | Task7 | Task | Active | 30003 | Project1 | Prabhat | 8 | 2 | 6 | TRUE | ||
40008 | Task8 | Task | Active | 30003 | Project1 | Prabhat | 3 | 1 | 2 | TRUE | ||
40009 | Task9 | Bug | New | 30004 | Project1 | Prabhat | 1 | 6 | 0 | 6 | TRUE | |
40010 | Task10 | Task | Active | 30007 | Project2 | Lokesh | 3 | 1 | 2 | TRUE | ||
40011 | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | TRUE | ||
40012 | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | TRUE | ||
40013 | Task13 | Task | Active | 30008 | Project2 | Rajesh | 3 | 0 | 3 | TRUE | ||
40014 | Task14 | Task | Active | 30008 | Project2 | Suma | 3 | 3 | 0 | TRUE |
Table-2: Revisions. This table has History records for all workitems from ALL projects where isCurrent = FALSE for all records.
WorItemId | Rev | RevDate | IsCurrent | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork |
40001 | 1 | 1-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | ||
40001 | 2 | 2-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 3 | 3-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 4 | 4-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 2 | 3 | ||
40005 | 1 | 1-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | ||
40005 | 2 | 4-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 3 | 5-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 4 | 6-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 5 | 7-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 6 | 8-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 4 | 2 | ||
40011 | 1 | 7-Jan-24 | FALSE | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | ||
40012 | 1 | 8-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | ||
40012 | 2 | 9-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40012 | 3 | 10-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40015 | 1 | 7-Jan-24 | FALSE | Task100 | Task | Active | 30089 | Project20 | Srinivas | 8 | 2 | 6 | ||
40016 | 1 | 10-Jan-24 | FALSE | Task101 | Task | Active | 30078 | Project20 | Srinivas | 8 | 2 | 6 |
I want to have a new table that will store ALL records from WorkItems and ONLY Matching records (Based on WorkItemId) from Revisions table. The Rev and RevDate columns can be NULL in the new table for the record that is coming from WorkItems.
Thanks,
Prabhat
Solved! Go to Solution.
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
36 |