Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Merging 2 files/tables by multiple columns

Daniela_Arenzana
5 - Atom

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:

ProductItemSales ChannelSum_Rolling18M_ASum_Rolling18M_BSum_Rolling18M_C
Shirt17800A2113975713
Shirt17800A53724623909
Shirt1800C45672354
Dress1695000D1448153
Dress1695000B51792317
Dress1695010B1512141
Dress1695010B1512141

 

File B:

ProductItemSales ChannelSum_YTD_ASum_YTD_BSum_YTD_C
Shirt17800A3113975713
Shirt17800A93989019995
Shirt1800D23332222
Coat670B18344
Coat670D2187109
Coat670D171585926
Coat670D3633481953

 

Desired Result:

ProductItemSales ChannelSum_Rolling18M_ASum_Rolling18M_BSum_Rolling18M_CSum_YTD_ASum_YTD_BSum_YTD_C
Shirt17800A21139757133113975713
Shirt17800A5372462390993989019995
Shirt1800C45672354   
Shirt1800D   23332222
Dress1695000B51792317   
Dress1695000D1448153   
Dress1695010B1512141   
Dress1695010B1512141   
Coat670B   18344
Coat670D   2187109
Coat670D   171585926
Coat670D   3633481953

 

Thank you very much in advance!

5 REPLIES 5
RolandSchubert
16 - Nebula
16 - Nebula

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
8 - Asteroid

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
5 - Atom

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
16 - Nebula
16 - Nebula

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
12 - Quasar

merge1.PNG

 

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  .  

 
Labels