Joining with Non-Unique Data Key- Creating Duplicates

Question

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 IDAccountVolumeCharge123account 1410123account 1410123account 2120456account 3115456account 4115

2020   Group IDAccountVolumeCharge123account 1410123account 1410123account 2120456account 3410789account 1410123account 456

Desired output:

Group IDAccountVolume 2021Charge 2021Volume 2020Charge 2020123account 1410410123account 1410410123account 2120120123account 4--56456account 3115410456account 4115--789account 1--410

Thank you!

danilang · Answer

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

YearIndependant.yxmd

Christina_H · Answer

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

Non-unique join.yxmd