Alteryx Designer

Definitive answers from Designer experts.

How to Combine Join Output Anchors with a Union Tool

Alteryx
Alteryx
Created

Understanding Join output anchors and when to add a Union tool:  The Join tool anchors are separate subsets of data. You can combine them with a Union tool. The Select functionality of the Join tool applies only to the J anchor, not the R- or L-anchored data.

Prerequisites
  • Alteryx Designer
    • All versions

Procedure

  1. If you've read the Tool Mastery article that covers the Join tool and then played with it on your own some, you've probably noticed some of its more subtle features.  One is that Joining two tables can generate a staggeringly large number of rows.  That's often due to selecting fields that have some repeated values, and is covered in a fine article titled "Why Your Join Is Getting More Records than Expected."

  2. The Alteryx Join tool has three outputs, L, J, and R as separate subsets of your data.  If you examine the output of the L (left), you will see the records that existed in the left data set, but do not match with records from the right.  The J (join) output provides only the records that are common between the two data sets.  And the R (right) output provides the records from the right that don't match with those on the left. 

    idea Skyscrapers
    You can pull data from only one of the anchors, (rotating to the less intuitive, but more common view)
    idea Skyscrapers
    or you can combine the outputs using a Union tool

    idea Skyscrapers

    idea Skyscrapers

    idea Skyscrapers

    idea Skyscrapers
  3. Suppose you want to change the names of the various fields or change their order.  If you are using the Join tool alone to work with just the J output anchor (the Inner Join situation) you can do everything at once and use the "Select" functionality built into the Join tool.  (If you are using any other anchors from the Join tool, please refer to this article for the best practices.)
    1. You can select/deselect, reorder, rename, or change the data types and sizes of the fields using the check boxes, using the arrows at the top, and typing in the grid.
      idea Skyscrapers
    2. You can use the Options to reduce the number of clicks compared to manually configuring the fields.
      idea Skyscrapers