Joining two data sets & consolidating data/removing empty cells

No-Sass

I'm joining two data sets with somewhat similar structure. The only parameter must be that the data coming in from the left side must be listed first and the data coming in from the right side listed in the rows second. If there is no data for a row in the left side then the data from the right side must be listed first...Example listed below

This is how the two data sets are currently being joined...

The results below show how the data presents itself on top and how we want the data to be on bottom...

The rub here is that this is a significant data set. I tried creating a record id and transposing the data vertically and ended up with a file that was 180m rows(original file is 1.8m rows). I wasn't sure that solution was going to be sustainable. Plus the top results file is about 100 columns wide w/ about 8 of these situations (Player, Unit etc)

I don't know if the solution to this problem lies in the original Join as shown above or if it's in addressing the results of that join...

(I usually get to this point when posting a challenge & get a solution to my question. This is the first time in MONTHS that this hasn't been the case so I'm kinda excited)...

Thoughts?!?

estherb47

Hi @No-Sass 

You were on the right path! If your data sets have the same number of records, then the easiest thing to do is join by record position in the first Join.

 

Then record ID, and transpose. Filter out the nulls/empties (the original order is still in tact). I find it easiest, since you want to combine the players into their columns, and then the units into their columns, to split into 2 streams. A Tile tool adds a number, which will be used in the Transpose tool as a column header. Dynamic rename to fix the names of the columns back to having Player and Unit, and then another Join, this time on RecordID. If anything falls out, the Union adds it back in. A sort tool puts the rows back in order.

This should work well, even with millions of records.

Let me know if it works for you!

Cheers,

Esther

JoinAndCleanColumns.yxmd