Join ToolJoin ToolThe 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 three 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.
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.
Drag a Join tool from the Tool Palette onto the workflow canvas.
Connect the two inputs to blend into the Join tool.
With the Join tool selected, configure the tool.
Select how to perform the Join. The two options are by record position, or by specific field.
Join by Record Position: Select this option when the two tables to be joined have the same field structure, and the data will be joined by its position within the two tables.
Join by Specific Field: Select this option when the two tables have one or more fields in common (such as an ID) and the data will be joined together. You can choose to Join on multiple fields. Each Join should be a separate row in the grid.
The Join tool restricts what field types can be joined together. See below for a list of possible error messages or warnings resulting from joining mismatched data types.
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.
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.
To delete a join field, click on the number on the left hand side and click the delete button 
on the right.
Use the table to modify the incoming data stream. Each row in the table represents a column in the data.
Select, deselect, and reorder columns
To include a column in data, select the check box to the left of the column name. Deselect the check box to exclude the column.
To reorder the columns of data:
or 
arrows, or right-click and drag, to move the rows to a new location.The Unknown column is selected by default. It allows new columns in the data. Move the column to the location where you want a new column to be.
Modify data type and size
To change the supported length (characters for string and numeric fixed decimal types) or measurement (bytes for other numeric types) of data in a column, click Size and type a number. Size varies by data type and can be edited for fixed decimal numeric types and all string types. See Data Types.
Use the [data type]: Forced option to ensure a column always contains the expected data type; this is helpful when creating macros. See Macros.
Rename a column or add a description
To change the name of a column, click Rename and type the new name.
To add a description, click Description and type a description.
View more options
After selecting or highlighting rows (columns of data) in the table, click Options to view more configuration options:
The Join tool restricts what field types can be joined together. The following error messages may result:
String fields can only be joined to other string fields.
Numeric fields can only be joined to other numeric fields.
Boolean fields can only be joined to other boolean fields.
DateTime fields types can only be joined to their exact type.
Spatial fields cannot be joined, use the Spatial Match Tool instead.
Blob fields cannot be joined to any other type.
Warning: Joins on Double or Float are not recommended due to rounding error.
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. |
©2018 Alteryx, Inc., all rights reserved. Allocate®, Alteryx®, Guzzler®, and Solocast® are registered trademarks of Alteryx, Inc.
