Alteryx Designer Desktop Discussions

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

Using multiple join with inconsistent inputs

DR711
6 - Meteoroid

I have about 20 different input files and my goal is to get them into one output file/tab with a column inserted to summarize. I have used an arrange tool to grab the two columns for each input however, I am running into a problem when trying to join the input files. These files have the same layout, but some have more data than others. I tried making a basic example below:

 

File 1:

 

ItemAmount
Bread1
Milk2
Cheese1
Eggs1
Total5

 

File 2:

ItemAmount
Mustard3
Meat2
Bread2
Mayo2
Lettuce1
Cheese3
Total13

 

File 3:

ItemAmount
Milk1
Eggs4
Cereal3
Waffles2
Fruit5
Total15

 

I would want to put it together as 

 

ItemSummaryFile 1 File 2File 3
Mustard3030
Bread3120
Milk3201
Eggs5104
Ceral3003
Cheese4130
Fruit5005
Meat2020
Lettuce1010
Mayo2020
Waffles2002
Total3351315

 

I created a table that included all of the items in the column 1 for each input file and then used join multiple by specific fields to try this on my inputs to join each file to the "item" column, but Alteryx returns an error that 236,459,580,739,200,000 records were generated by the join and only 20 are displayed. However, I can't do join multiple based on record position because some files have a slightly different list of items than others and the amounts go into the wrong rows. Am I missing something in my multiple join by field that is causing so many possible records?

3 REPLIES 3
binuacs
20 - Arcturus

@DR711 One way of doing this with the cross tab tool

 

binuacs_0-1675718072507.png

 

apathetichell
18 - Pollux

As @binuacs pointed out - this is a Union - not a join. If you want to do this dynamically with tons of files - you'd use a batch macro which assigns the filename to a new column...

DR711
6 - Meteoroid

Thanks to you both. I believe I've made my example simpler than what I need to accomplish. Using the three FakeCo files below, how could I get to the FakeCo Summary file? The summary piece I am on board with, but post union I am unsure how to use cross-tab to generate all of the different items that should go under deductible, perpetual differences, and short-term differences under one column and then values for each of the Co's. Would cross-tab still be the appropriate tool here? I am assuming I would need to use multiple?

Labels