Introduction to Append, Joins, and Union in Alteryx.

When you first start working with Alteryx, some of the most commonly used tools are Append, Join, and Union. These tools are located in the Join tab, which is marked with a purple icon. As their names suggest, they are used to combine and merge data from different sources.

In this guide, we’ll introduce each of these tools, explain their differences, and provide examples of how they work.

1 – Appends

 

It adds the columns from a source input to every row in the target input. The tool has a “T” that refers to the Target input and “S” to a source input.

The tool has two key inputs:

• T (Target Input): Usually the main table.
• S (Source Input): The additional information to be appended to the main table.

 

 

Outputs:

Data from the Source input will be added to each row at the “Target” table, creating many rows to the table. Be mindful that this can impact workflow performance.

Table 1 – Stores

 

Table 2 – States

 

 

In the example below, there are two tables: Stores and States. If we set Stores as the target input and States as the source input, the output table will add a state to every store record.

 

 

When we do the opposite, the outcome table will have the addition of all the stores for each state.

 

2 – Unions:

It combines two or more tables with similar structures (same column names).

 

Output:

Columns will have the data from both combined tables.

Table 1 – Store_ID expenses

 

Table 2 – Store_ID expenses2

 

In the example below, the number of records will increase to 20, 10 from each table (Store_ID expenses and Store_ID expenses2).

 

 

 

3 – Joins:

It is a combination of two data sources based on common fields. The tool has two inputs,  “L” for left and “R” for right side.

 

Outputs:

As an example, we will use the same tables from the Union tool: Store_ID Expenses and Store_ID Expenses2. We will demonstrate all possible outputs using Store_ID as the common field linking both tables.

 

 

Left join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “L” anchor.

 

Right Join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “R” anchor.

 

Inner Join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “J” anchor.

 

 

“L” Outer Join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “L”  and “J” anchors. This join will combine all the records from “L” input plus all the records that are from the common field (Store ID) from “L” and “R” inputs.

 

 

 

“R” Outer Join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “R” and “J” anchors. This join will combine all the records from “R” input plus all the records that are from the common field (Store ID) from “L” and “R” inputs.

 

 

 

 

Full Outer Join: Store_ID expenses linked to the “L” input and Store_ID Expenses2 connected to “R” input. Output on “L”, “R” and “J” anchors. This join will combine all the records from “L”, “R”, “J” anchors.

 

 

 

 

Conclusion

This blog provided a brief overview of the Append, Union, and Join tools commonly used in Alteryx. While these tools share similarities, they also have key differences and can be used in various situations.

 

I hope you enjoyed. See you next time.