Append rows from 2 tables into a new table based on Matching column value

[prabhatnath]

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

[prabhatnath]

Thank you @selimovd . That is exactly what I was looking for.

- Prabhat

[selimovd]

Hey @prabhatnath ,

where are you struggling?

And what did you try already?

Best regards

Denis

[selimovd · Accepted 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