Join multiple tables but only adding dates when necessary

[bcirka]

Looking to join two sets of financial data to one set of project header data.

Ex.

Table 1:  Project Header Data

Project Name	Proj Attribute 1	Project ID
A	D	1
B	E	2
C	F	3

Table 2:  Financial Data A

Project ID	Project Actual Spend	Actual Spend Date
1	50	01/01/2020
2	100	05/01/2021
3	200	05/01/2020

Table 3:  Financial Data B

Project ID	Project Forecast Spend	Forecast Spend Date
1	200	01/01/2020
2	400	10/01/2021
3	800	08/01/2020

I'm good joining tables 1 and 2.  The join brings the dates associated with Table 2 into the project header table.

Where I get stuck is trying to join Table 3.  Where dates already exist (having been joined between table 1 and 2), I'd like it to put the Table 3 data in the same row, but in a different column.  Where Table 3 has a new date, I'd like it to create a new row and add it.

Feel like that's a bit confusing...if clarification is needed, let me know.

[MarqueeCrew · Accepted solution]

@bcirka ,

I think that you're looking to create a 2nd JOIN using PROJECT NAME and DATE.  When the JOIN doesn't happen, you can follow the JOIN with a UNION tool.  The UNION allows you to use the J output anchor and the R output anchor as inputs.  This will create another row.  There are one-tool examples about UNION.

Cheers,

Mark

[bcirka]

That worked perfect.  Thanks for the guidance--really appreciate it.