Joining with Non-Unique Data Key- Creating Duplicates

[lomeoari]

Hi all,

I am struggling to join some data tables I have. When using the Join I am getting duplicate outputs since the key I  am trying to join by is not unique, and tables have different amount of data. I tried assigning records ID's and joining that way but still no luck.

Below is an example of how my data is currently setup in separate tables. I need to join these two tables but not have duplicate data created. 

2021
Group ID	Account	Volume	Charge
123	account 1	4	10
123	account 1	4	10
123	account 2	1	20
456	account 3	1	15
456	account 4	1	15

2020
Group ID	Account	Volume	Charge
123	account 1	4	10
123	account 1	4	10
123	account 2	1	20
456	account 3	4	10
789	account 1	4	10
123	account 4	5	6

Desired output:

Group ID	Account	Volume 2021	Charge 2021	Volume 2020	Charge 2020
123	account 1	4	10	4	10
123	account 1	4	10	4	10
123	account 2	1	20	1	20
123	account 4	-	-	5	6
456	account 3	1	15	4	10
456	account 4	1	15	-	-
789	account 1	-	-	4	10

Thank you!

[Christina_H]

You were on the right lines with the record id, you just need to generate your own for each group.

[danilang]

Hi @lomeoari 

Here's a different way to do it.  It builds the column names dynamically, so it will run with (almost) no change in 2022.

I say "(almost) no change", because the piece that needs to change is the Year Info text inputs.  These hold the year that is associated with the data and needs to be included on each row of data.  If your actual data includes this in some way, i.e. Transaction Date, then use the existing year data from this.  In your example, you have the year included as the first row of your tables.  If your data is similar to your sample use this method to convert each year's data and attach the output to the union, deleting the Current Year and Previous Year containers.  

Dan