During DS31’s first week of training, we learnt how to join tables of data together in Alteryx and Tableau Prep, which is very useful if you would like to include additional information about records but the corresponding data is in a different data source.
An inner join only outputs rows that share a specified field in both tables. A left outer join outputs the inner join plus any records from the left table that were not matched to any rows in the right table. A right outer join is the same concept, but with the right table instead.
In Alteryx, two tables with common fields can be joined using the Join tool (surprise!).
For example, we may want to use country names in the sales team table above, instead of country codes. We could use an IF statement in a calculated field, but it would be much faster to join it to the table below, matching every country code with a name.
We connect these inputs to the join tool and join by specific fields – namely the country codes (these two fields need not be named the same). Unchecking the Country Code field from the right table in the output is good practice, as otherwise we will end up with two identical Country Code columns.
However, the Join tool in Alteryx has the shortcoming that it cannot execute left or right outer joins. It only outputs rows from the inner join and, if there are any, rows from the left and right tables that could not be matched up.
Tableau Prep does provide the option for any kind of join. In the workflow below, an inner join is being used to add a sales target to store sales data, linking the two tables via the Store and Quarter fields.
The join type is set to inner, but in the settings you can change this to whichever join type is relevant for your project:
