Merging Tricky Sheets in Alteryx and Tableau Prep

by Nicholas Bowskill

Tableau Prep is very user friendly and easy to use, but Alteryx has a much wider range of tools it can use in data preparation. Using Alteryx before learning Tableau Prep, something I was surprised at with Prep is the ability to wildcard input files with different sheet structures which I found very useful. Prep then has a very nice way to merge and rename columns. For this example I will use the data files that showed me this feature in Prep, about register dog names in Zurich. The Excel data looked like:

Wildcarding in files with different structures:

Alteryx has problems with wildcarding files that don’t have the same sheet structure (field names, field positions or sizes). If a wildcard is executed on files with a different sheet structure then this error occurs:

 

However in Tableau Prep no such error occurs. Instead the files are brought together in this structure:

The process to merge fields:

As the same information is held in the sheets but with different field names and field positions Alteryx needs two inputs to bring in both files. Once these are both brought in separately then a union can be used to bring the 6 fields into the 3 that want to be used. However since the field names and positions are different the ‘Manually Configure Fields’ option for the union has to be used:

The second set of field names have to be manually moved into position under its corresponding first field name. Once this is done then the workflow can be run and the fields will be together.

 

In Tableau Prep the process is a lot more user friendly. Adding a Step onto the input allows the user to change the data structure.

The on the step the two corresponding fields can be selected, by clicking on the first and then Ctrl + clicking on the second. Once both fields are selected the merge button will appear next to the ‘Create Calculated Field’ option. Pressing the merge button will bring these two fields into one.

This does have to be done for each of the three fields rather than in one tool, but this is still much more user friendly in my opinion.

 

Renaming the fields

In Alteryx a Select tool is a simple way to rename all of the fields. After the tool has been placed into the workflow the user can then edit each of the names from within the tool. And if the field positions need re-positioning this can be done in here too.

 

Again Tableau Prep has a very user friendly answer to this. All the user has to do is to is double-click on the field names and the option to rename will come up.

Once all the field names are changed in this way then the data is altogether and ready to go!

This was a trick that I found very nice when learning how to use Tableau Prep and I hope you find it useful too.

Avatar

Nicholas Bowskill

Sun 20 Jan 2019

Sat 19 Jan 2019

Tue 08 Jan 2019