Using Alteryx to Append Fields with a Lookup Table

by Kofi Bonsu

While attempting the recent NHL Makeover Monday, I decided to supplement the attendance data with stadium capacity data. I found some data that would work and after some quick cleaning of the data set, added it to the NHL data on Tableau. I ended up with a lot of null values due to the team names of the 2 datasets not matching.
To rectify the problem I used an Input tool to load the NHL stadium capacity data back into Alteryx to change the names. This is what the incoming dataset looked like.

To do this I used a Text Input tool to create a lookup table, with one column containing the incorrect names in the column ‘Original Location’ and the other column named ‘Clean Location’ with the correct names.

I then added a join tool to join the typed lookup table connected to the right input with the capacity data connected to the left input, and joined on the Team names.
Left (Teams) <=> Right (Original Location)

In Alteryx the inner join is populated by the records from the left input and records from the right input that have matching records, in this case team names. The left join is populated by records from the left input that don’t have any matches with the right input, and the right join is populated by records from the right input that don’t have any matching records with the left input.

This gave me the output from the inner join – in the image below –  with a table showing the team and capacity columns from the left join and the original location and clean location columns from the right join, as there were the same name locations in both ‘team’ and ‘original location’ columns.

A select tool was used to remove the original location column as this is no longer needed, and also renamed the clean location column to team to match the capacity data column titles. The original team column was then removed. This left me was this, matching the format of the capacity data.

I then used an Output tool to export my table into Tableau.


© 2022 The Information Lab Ltd. All rights reserved.