You are here: Tools > All Tools > Join

Join

The Join tool combines two inputs based on a common field(s) between the two tables. You can also Join two data streams based on record position.

The 3 outputs resulting from the join are as follows:

L Output: contains records from the L input that did NOT join to records from the R input.

J Output: contains records that joined from the L input to those records in the R input.

R Output: contains records from the R input that did NOT join to records from the L input.

 

If another type of join is desired, see below.

Why you would use this tool

You may want to use a Join tool to blend two data sources, point of sale and loyalty card data that share a common Store ID.

How to use this tool

  1. Drag a Join tool from the Tool Palette onto the workflow canvas.

  2. Connect the 2 inputs to blend into the Join tool.

  3. With the Join tool selected, configure the tool.

  4. Select how to perform the Join. The two options are by record position, or by specific field.

      1. Each Input will have a drop down list of its fields. Select the join field for each input. Alteryx will automatically select a join field from an input if the same field name was already selected from a different input.

      2. If multiple join fields are desired, an additional row of join fields can be configured. Simply click on the drop down to choose additional join field per input.

      3. To delete a join field, click on the number on the left hand side and click the delete button on the right.

  5. From the configuration properties, you can rename fields, reorder fields, sort ascending/descending, and select the fields needed from the inputs. See Options for more information.

Click Apply to have the configurations accepted.

For information regarding Input, Output, Annotation and Error Properties, see Tool Properties.

Data Joins and Data Types

The Join tool restricts what field types can be joined together. The following error messages may result:

Doing different types of Joins

See the table below for using the Join tool to execute different types of joins.

Inner Join:contains records that joined from the L input to those records in the R input.

The J output of the Join tool contains the result of an Inner Join.

Left Unjoin: contains records from the L input that did NOT join to records from the R input.

The L output of the Join tool contains the result of a Left Unjoin.

Right Unjoin: contains records from the R input that did NOT join to records from the L input.

The R output of the Join tool contains the result of a Right Unjoin.

Left Outer Join: all records from the L input including the records that joined with the R input.

To do a Left Outer Join, connect the J and L outputs of the Join tool to the Union tool.

Connect the J output first to establish the combined table schema.

Right Outer Join: all records from the R input including the records that joined with the L input.

To do a Right Outer Join, connect the J and R outputs of the Join tool to the Union tool.

Connect the J output first to establish the combined table schema.

Full Outer Join: all of the records from both L and R inputs.

To do a Full Outer Join, connect the J, L, and R outputs of the Join tool to the Union tool.

Connect the J output first to establish the combined table schema.

 

Related Topics Link IconRelated Topics