Merging 2 files/tables by multiple columns

[Daniela_Arenzana]

Hello!

I've been trying to merge 2 files in order to create a third table where IF the 3 first columns are IDENTICAL then it adds to the right the information of the second table. If aren't equal leave the cells empty if not info for that column but still add the new content to the new table.

File A:

Product	Item	Sales Channel	Sum_Rolling18M_A	Sum_Rolling18M_B	Sum_Rolling18M_C
Shirt	17800	A	2	11397	5713
Shirt	17800	A	5	37246	23909
Shirt	1800	C	4	567	2354
Dress	1695000	D	1	448	153
Dress	1695000	B	5	1792	317
Dress	1695010	B	1	512	141
Dress	1695010	B	1	512	141

File B:

Product	Item	Sales Channel	Sum_YTD_A	Sum_YTD_B	Sum_YTD_C
Shirt	17800	A	3	11397	5713
Shirt	17800	A	9	39890	19995
Shirt	1800	D	2	333	2222
Coat	670	B	1	83	44
Coat	670	D	2	187	109
Coat	670	D	17	1585	926
Coat	670	D	36	3348	1953

Desired Result:

Product	Item	Sales Channel	Sum_Rolling18M_A	Sum_Rolling18M_B	Sum_Rolling18M_C	Sum_YTD_A	Sum_YTD_B	Sum_YTD_C
Shirt	17800	A	2	11397	5713	3	11397	5713
Shirt	17800	A	5	37246	23909	9	39890	19995
Shirt	1800	C	4	567	2354
Shirt	1800	D				2	333	2222
Dress	1695000	B	5	1792	317
Dress	1695000	D	1	448	153
Dress	1695010	B	1	512	141
Dress	1695010	B	1	512	141
Coat	670	B				1	83	44
Coat	670	D				2	187	109
Coat	670	D				17	1585	926
Coat	670	D				36	3348	1953

Thank you very much in advance!

[RolandSchubert]

Hi @Daniela_Arenzana ,

it should be possible to merge data this way by using JOIN tool and a UNION tool. A problem is, that there are duplicate records in the columns you want to use as a criterion (e.g. Product Shirt /Item 17800/Sales Channel A) leading to strange results. So maybe you need an addition column to JOIN?

Best regards

Roland

[TSP · Accepted solution]

Yes as @RolandSchubert says, there is nothing unique about the first row in File A in order to determine it should only be matched to the first row in File B.

If you are content to have those first two rows merged into a single output row then it is possible, but with your current data your desired output is not possible UNLESS you want to simply match the first iteration of each row based on the order it is currently sorted - but that would not be particularly meaningful I would imagine.

Regards

Tim

[Daniela_Arenzana · Accepted solution]

Hi @RolandSchubert & @TSP !

Thank you for your response and feedback!

You both are correct there's nothing unique about the first row in File A in order to determine it should only be matched to the first row in File B. As a next step in my workflow I was going to have those 2 merged into one row. I tried to join and union before posting my question but it doesn't matter how many changes to the criteria I tried it was duplicating the data for those that matched the criteria of having the 3 first columns identical. 😞

Are you suggesting to Sum them first and then join and union?

[RolandSchubert · Accepted solution]

Hi @Daniela_Arenzana ,

I think so. If you first aggregate row with identical columns A-C, JOIN and UNION should work fine. The value columns seem to be appropriate to sum.

Best regards

Roland

[benakesh · Accepted solution]

Hi @Daniela_Arenzana ,

The wf produces expected result .  The  tile tool  produces  sequence number within group and  this is  4th element in key .

As others have suggested  it is better to add  row number  as  4th element  to make sure that  there are no duplicates  in  record keys  .