Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

What's the Difference Between the Append, Join and Union Tools?

dawnh80
8 - Asteroid
When I teach Alteryx to new users, this is a question that comes up a lot. I modified the below picture from the internet to help explain these tools.

 

AppendJoinUnion.jpg

With the Union Tool you stack data sets. In the picture above, the data from Table 1 is read first and then the data from Table 2 is added underneath the data from Table 1.

 
 

With the Join Tool, especially the J output anchor, you combine data from both tables. You will have new columns containing the data from the second table. It joins the tables on the similar column they have in common which is the Fruit column. In the example below it combined the data from Table 1 and Table 2 to have Color and Shape in a new data set for the corresponding Fruit.

 

Jointooldata.jpg

 

If you took these same tables and used the Union Tool, you would have the results below;

 

UnionResult.jpg

 

Notice Table 2 data is directly after Table 1 data. There are Null values for the Shape column for Table 1, since Table 1 did not have this column in it. The same is true for the Color column for Table 2.

 
 
 

What about the Append Tool? This tool simply adds whatever data you connect your Source data (Table 2 in pic below) set to your Target data (Table 1). If you have one column it will add the field for every single row of your data set in a new column as shown below;

 

AppendFieldResults1.jpg

 

If you were to join the original two tables together using the Append Tool you would get every possible combination of columns and rows for both data sets. This is called a Cartesian Product. It joins the data from all tables without requiring any joining references. See the below picture for details.

 

AppendFieldResults2.jpg

There are certain circumstances where you may want to use the Append Tool in this way.

 

What if you were a car company and you needed to combine every car with every color interior and exterior. You would use this tool to create that data.

 

Hopefully these examples above help with the distinction between these tools.

4 REPLIES 4
JagdeeshN
12 - Quasar
12 - Quasar

@dawnh80  Thanks for sharing

Emil_Kos
17 - Castor
17 - Castor

Hi @dawnh80,

 

Great work!

 

HomesickSurfer
12 - Quasar

Hi @dawnh80 

 

Love it!

 

If I may suggest, perhaps include the Find Replace tool as it does a very effective and efficient job at appending fields.

ciganomad
6 - Meteoroid

Thanks for clear teaching !!! 

Labels