Alteryx Designer Discussions

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

Join Multiple - Merge Input Fields into Single Column

CGIQV
8 - Asteroid

I couldn't think of a proper title, but I'll try to explain here.

 

Let's take three data sets:

 

Input 1

FruitColor
AppleRed
PearGreen

 

Input 2

FruitRipe?
Apple

Yes

PearYes

 

Input 3

FruitCostAmount
Apple1.005
Pear1.50

2

Peach0.50

1

 

I'd like to join all three data sets so it looks like this:

 

Desired Result:

FruitCostAmountRipe?Color
Apple1.005YesRed
Pear1.50

2

YesGreen
Peach0.50

1

  

 

 

I set "Join by Specific Fields" and list "Fruit" as all 3 inputs.

 

On the outputs, I select all fields including Cost, Amount, Ripe and Color.  I also need to select "Fruit" from one of the inputs.  The problem is, if I select from input #1 or input #2, peach does not show up.  if I select from #3, Apple/Pear do not show up.  If I select from more than one, we end up with a result like this:

 

Fruit#1CostAmountRipe?ColorFruit #2
Apple1.005YesRed 
Pear1.50

2

YesGreen 
 

0.50

1

  Peach

 

I thought about a union tool, but then will simply stack them on top of one another rather than layer them in as columns.

 

Any idea how to get this one to work without some cumbersome workaround?

7 REPLIES 7
BrandonB
Alteryx
Alteryx

Workflow is attached. The issue is that you don't have a 1 to 1 relationship across all three of your inputs. This method should do the trick.

mmenth
11 - Bolide

Hi @CGIQV,

 

Whenever I want to control the records that do not join, I tend to shy away from using the multiple join tool and just bring multiple regular joins on the canvas so that I can union after each individual join. In the attached example, it would give you the desired output no matter which combination of fruits are in each file.

 

multiple joins.PNG

 

Best,

mmenth

NickSm
Alteryx
Alteryx

@CGIQV 

 

The results look a bit incomplete at first because of not having a direct match for a join across all three inputs.  Attaching a workflow with a couple ways to get the desired output - one join at a time and an outer join, or using that Join Multiple with a select tool to re-organize the fields.

CGIQV
8 - Asteroid

Yep, that's exactly what I was thinking.

 

I was literally typing out a response to my own post and clicked back quickly and saw your workflow which appears to do exactly what I was trying to explain below....

response.GIF

CGIQV
8 - Asteroid

@NickSm 

 

Option 2 on this one seems to be the most straight forward.

 

Thank you.

 

EDIT: In hindsight, this would only work if there was a single input that encapsulated all fruits in the dataset.  In this case, input #3 does, but this may not always be the case.

NickSm
Alteryx
Alteryx

@CGIQV  - Totally correct, it's definitely the less dynamic of the options.  To be the most flexible you'd want to use joins/unions to do complete outer joins to make sure you're not missing anything from the Join Multiple.

BrandonB
Alteryx
Alteryx

You can also add a formula after the union that say if fruit field is null, use right fruit field which would take care of those situations.

Labels