Put 2 datasets next to eachother.

[The1804]

Hi,

I'm looking for a simple solution to put 2 data sets next to eachother but without generating all possible combinations. Unfortunately I wasn't able to find anything in existing topics.

Here are the inputs:

Key	Value1
Key1	12
Key2	543
Key2	765
Key2	987

Key	Value2
Key1	32
Key1	12
Key1	321
Key2	765
Key2	876

And expected output:

Key	Value1	Value2
Key1	12	32
Key1		12
Key1		321
Key2	543	765
Key2	765	876
Key2	987

All help is appreciated!

[Emil_Kos]

Hi,

Join tool is the perfect tool to achieve it. You need to pick the option join by record position. 

[mceleavey]

Hi @The1804,

we're lacking a bit of information here as the join logic is not consistent.

As @Emil_Kos points out, you can join by position which will give you all records from the first dataset with any corresponding sets in the same record potion, but does not account for those records which are joined.

Can you confirm the join logic between the two, as the first dataset seems to retain the order of the Key2 value, but not the key1 value, which is taken from the second dataset.

M.

[The1804]

Hi @Emil_Kos

Maybe I wasn't clear enough. I still need to join by Key value but I just need to list all values from both sources next to eachother.

Joining by record position would only work if the amount of values per key would always be the same in both sets.

I hope you understand what I mean.

[mceleavey]

@The1804 ,

Given what I went over previously, the following workflow isolates the key value in each dataset, uses the join by record position method, and unions back together:

But this method needs the logic to be determined for it to be consistent.

M.

[AngelosPachis]

Hey @The1804 ,

Based on your input, you want to join on key ID and a new field that you have to create (I named it ID) which numbers the number of records for each ID.

Then you can use a join or join multiple to bring everything together.

Hope that helps.

Angelos