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!

Tool Mastery

Explore a diverse compilation of articles that take an in-depth look at Designer tools.
Become a Tool Master

Learn how you can share your expertise with the Community

LEARN MORE

Tool Mastery | Join Multiple

JoshKushner
12 - Quasar
Created
Join Multiple.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Join Multiple Tool on our way to mastering the Alteryx Designer:

 

TheJoin Multipletool is a combination of theJoinandUniontool. It is best used to consolidate a workflow into one tool when you would instead use multipleJoin andUnion tools:

 

1.png

 

Join Multiple can output all joins (Left, Inner, and Right):

 

2.png

 

Or output only an Inner Join with "Only Output Records that Join from All Inputs" checked:

 

3.png

 

This functionality, combining Join and Union, can be revealed through two test cases:

  1. Join Multiple with identical join criteria
  2. Join Multiple with different join criteria

 

  1. Joining multiple inputs with identical join criteria is the easiest because the output is exactly what is expected (an inner join). Because there won’t be any left or right join outputs the join multiple tool should only be used to join more than 2 inputs at once. Otherwise a regular join tool would work just as well.

5.png

 

  1. Joining multiple inputs with different join criteria really makes use of the union function. If there is ever an output from the left or right join it will just be Null in the output for the inputs that didn’t match.

 

Input1:6.png

 

Input 2:7.png

 

Input 3:8.png

 

Output:9.png

 

This function is especially useful when preparing reports because the reporting tools don’t care if there are null columns in a row.

For example, joining a Table and Report Text creates an output that doesn’t match:

 

10.png

 

11.png

 

But the Layout tool still creates a proper report:

 

12.png

 

By now, you should have expert-level proficiency with the Join Multiple Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts everyTool Tuesdayby followingAlteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications.

Additional Information

Click on the corresponding language link below to access this article in another language -

Comments
sonseeahray
8 - Asteroid

@JoshKushner Hi Josh,

I am trying to use the Join Multiple tool for the first time and am receiving the following error:  "Join Multiple (31) 680625 records were generated by an N-Dimensional Join - Join Key: (Po Id: )" What am I doing incorrectly?

 

sonseeahray_0-1581523444110.png

 

kirill1
5 - Atom

Hi @sonseeahray , sorry for the late reply.

 

I believe this is an error due to the Cartesian joins present (when every row of one table is linked to every row of another table)
Try selecting "Allow All multidimensional Joins" in the Cartesian Joins drop-down menu. This will suppress the error.

 

More information here: https://help.alteryx.com/2018.2/JoinMultiple.htm

leahpenelope
7 - Meteor

Hi Josh,

will this tool error out if one or two of the tables out of 3 are not present?

if yes what is a good workaround to joining three tables with identical structures and potentially no common fields... and one or two of the tables may not be present each time the canvas runs?

Ideally I would like to stack multiple tables horizontally and not get an error if one of the tables is not present, something akin to using the union tool just stacking horizontally instead of vertically.

Thanks

FláviaB
Alteryx Community Team
Alteryx Community Team

Hi @Atulaimk23. I am moving you question to our Designer Discussions board for more visibility. 

james_war
5 - Atom

I created an account solely to request that this tool is enhanced to include an option for:

 

"left outer join all inputs to input 1".

 

It would honestly save so much heartache. I know Alteryx-style is to union inner and left joins together but please just allow us to LOJ multiple inputs together in one tool.

lepome
Alteryx Alumni (Retired)

@james_war 
Because you've already got a great idea and an account, please consider re-posting to the Designer Ideas page.