Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Tool Mastery | Union

Alteryx Community Team
Alteryx Community Team
Created

Union.pngThis 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 Union Tool on our way to mastering the Alteryx Designer:

 

The Union Tool, the aptly named join category tool with DNA on it, accepts multiple input streams of data and combines them into one, unified, data stream. Whereas the Join Tool combines datasets horizontally (either by a record ID or record position), the Union Tool combines datasets vertically. Not unlike how two nucleic acid strands are unified to form the double helical DNA.

 

We know, great puns are in our DNA.

 

The Union Tool has a handful of great applications besides side-stitching punchlines, too. Check them out below:

 

  • Stacking records

Have common fields in multiple datasets? Stack them into a single stream with the Union Tool by field name, position, or with manual arrangement:

 

stacking like records.PNG

 

Don't worry - your datasets don't have to have identical. Any uncommon fields will be at the end of the table, with any fields that are not in a given dataset being populated with null values.

 

  • Creating different joins

The Alteryx Join Tool has 3 outputs:

 

alteryx joins.PNG

 

These look like:

 

JOIN 1.PNG

 

If you’re used to SQL joins, these are the left, inner, and right joins, respectively. The Union Tool allows you to effortlessly combine these Join outputs (shaded areas above) to create other, more complex, SQL join configurations like the ones below:

 

JOIN 2.PNG

 

  • Combining reporting elements vertically

Simply take your reporting elements and specify an Output Order in the Union Tool to stack them vertically - without creating a single reporting element from the combination like the Layout Tool does:

 

reporting.PNG

 

See the attached workflow, Union.yxmd, for the stack, join, and reporting examples shown above!

 

By now, you should have expert-level proficiency with the Union 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 every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Attachments
Comments
5 - Atom

How can I add a column to output to indicate source of input data ( such as from 1st source or second or third etc. )?

Alteryx Partner

According to the graphics, while the middle output of the join tool is indeed equivalent to a SQL inner join, the other two outputs do not represent left and right joins, but probably the result of an except statement. SQL left and right joins are represented by the graphics shown after the unions are applied. 

5 - Atom

Great Articles... Making Learning easy and fun... 🙂

 

I had a query with respect to Union Tool -

 

Without specifying any output order (Keeping 'Set a specific Output order' tab unchecked)

Is there a default ordering behaviour associated with the Tool? If not, Wouldn't it be great to have a default ordering such as 1st data set should always be the one on the top and then the 2nd data set irrespective of the type of options used for performing the Union tasks.

 

 

 

5 - Atom

Is it possible to select multiple inputs and drag them all to the Union tool at once?