Joining data is one of the most fundamental data analytics skills. Joining data essentially means to combine two or more sets of data from a common field or fields. There are different types of joins that fulfils different goals. Inner joins, left joins, right joins and full outer joins. In Alteryx, the tools used to achieve these different types of joins might not be so straight forward when compared to other tools or languages like Tableau Prep or Python where each type of join has their parameter to set. That is why in this blog, I will go through how to perform these different types of joins in Alteryx.
First of all, some quick definitions of the join types listed in the paragraph above:
- Inner Join: Returns records where the keys exist in both tables.
- Left Join: Returns all records from the left table. Even if they don’t exist on the right table.
- Right Join: Return all records from the right table. Even if they don’t exist on the left table.
- Full Outer Join: Return all records in both of the tables.
The Join Tool
To explain the joining procedure in Alteryx, we will need to understand how the “Join” tool works. Below is the Join tool. It has two input anchors and three output anchors. The “L” and “R” input anchors indicate the left and the right table that is to be connected for a join operation. For the “L”, “J” and “R” output anchors, the “L” indicates records from the left table that did not match. The “R” indicates the same but for the right table. And the “J” output anchor indicates all records that matched from both left and right tables. The graphical representation of the output anchors also represents their function in the configuration window below.
Inner, Left, Right and Outer Joins in Alteryx
If I had a Browse Tool connected to the “J” anchor. I would be viewing only the records that matched from both the tables. Hence being an inner join. But what about left, right and full outer joins?
To make a left join, we would need to make use of both the “J” and the “L” anchor to capture record on the left table as well as records that matched with the right table. In order to achieve this, we need to make use of another tool to achieve this. The “Union Tool”. Below is a representation of a left join in Alteryx.
As it can be observed, The union tool has two incoming connections from both the “L” and “J anchor”. The union tool can have multiple connections coming in. It is concerned with combining two or more tables with similar structures based on field names or positions. As it can be guessed, doing a right join and a full outer join is very similar to this configuration. Only the connections are altered. Below is the configuration for a right join and a full outer join.
Even though doing other types of joins than an inner join in Alteryx might not be very straight forward. It is quite easy to perform these other types of joins once you know how the configuration is set. Happy joining!
Visit our site The Information Lab NL to see more blog posts, training and consultancy services regarding Tableau, Alteryx and Snowflake.