2019: Week 26 Solution
You can view our full solution workflow below, download it here, or view the walkthrough video further down!
This week we’re using straight out-the-box Tableau Prep and taking away the power of writing calculations in order to highlight some of the great features that are built into it. Some of the things used within this challenge are:
And all this without having to do any typing other than renaming some fields for clarity! This will be quite a simplistic write-up, but that’s just a reflection of how simple all these tasks are once you know how to do them.
After clicking split you should now have 6 new fields: [Ingredients – Split 1] to [Ingredients – Split 6]. The fact that these include the ingredient number in the field name will come in handy later! We can also delete the original [Ingredients] field now as we don’t need it in the final output.
Either drag all the [Ingredients – Split X] fields from the ‘Pivot Settings’ to the ‘Pivoted Fields’ area, or click ‘Use wildcard search to pivot’ and type ‘Split’ to automatically add any field containing the word ‘Split’.
This generates two new fields:
There’s two quick ways to do this:
This removes everything from the [Pivot1 Names] field apart from the number!
This will create a new field called [Pivot1 Names – Split 1] which contains just the number as we’re splitting off everything after the last space in [Pivot1 Names]. Remove the original [Pivot1 Names] field after this.
After either of the previous options, rename the final field to [Ingredient Position].
- GROUP on the [Ingredients] field.
- AVG the [Cocktail Price] field.
This gives us the average price for each ingredient across the whole data set. Rename the newly aggregated field to [Avg Ingredient Price].
To attach this back onto our full data set we need to add a ‘Join’ step which joins our full data to our average ingredient price data. To create a join simply click and drag the Aggregate step of the right-hand side of the step containing the full data. You should see a ‘Join’ box appear, at which point release your mouse click.
By default this creates an ‘inner’ join and Tableau Prep attempts to automatically join related fields. In our case in correctly automatically joins on [Ingredient] = [Ingredient]! The final step is to simply remove one of the now duplicated [Ingredient] fields.
 |
| Our full workflow solution. |
This week we’re using straight out-the-box Tableau Prep and taking away the power of writing calculations in order to highlight some of the great features that are built into it. Some of the things used within this challenge are:
- Splitting a field up into multiple fields.
- Pivoting columns to rows.
- Filtering data.
- Cleaning up data (optional).
- Aggregating data.
- Joining data together.
And all this without having to do any typing other than renaming some fields for clarity! This will be quite a simplistic write-up, but that’s just a reflection of how simple all these tasks are once you know how to do them.
Splitting up the ingredients.
Click on the [Ingredients] field and click ‘Custom Split…’ (either from the profile pane toolbar or by clicking the ‘More Options’ dots on the field itself & clicking ‘Split Values’). Set the separator as ‘,’ and set the split settings to ‘All fields’.After clicking split you should now have 6 new fields: [Ingredients – Split 1] to [Ingredients – Split 6]. The fact that these include the ingredient number in the field name will come in handy later! We can also delete the original [Ingredients] field now as we don’t need it in the final output.
Pivoting the ingredients into a single column.
After splitting the [Ingredients] field we now need to add a new pivot step. By default it should be set to ‘Column to Rows’ which is exactly what we need as we need to take all the [Ingredients – Split X] values and stack them on top of each other.Either drag all the [Ingredients – Split X] fields from the ‘Pivot Settings’ to the ‘Pivoted Fields’ area, or click ‘Use wildcard search to pivot’ and type ‘Split’ to automatically add any field containing the word ‘Split’.
This generates two new fields:
- [Ingredients Split] : This contains all the ingredients.
- [Pivot1 Names] : This contains all the original [Ingredient – Split X] field names.
Filtering out empty ingredients
In the profile pane you should notice a large amount of blank values for [Ingredients] split. This is because many of the fields were empty as not all cocktails had 6 different ingredients. To remove all these rows simply left click on the empty value and select ‘Exclude’ from the profile pane toolbar or right rick on the empty value and select ‘Exclude’.Numbering the ingredients
To number the ingredients we don’t need to use any fancy calculations as the split & pivot has automatically numbered them for us – we just need to obtain the number from the [Pivot1 Names] field!There’s two quick ways to do this:
1. Use the ‘Clean’ option
Click on the [Pivot1 Names] field and then the ‘More Options’ dots. You should see a ‘Clean’ option. From here, use the following three options in turn:- Clean > Remove Letters
- Clean > Remove Punctuations
- Clean > Remove All Spaces
This removes everything from the [Pivot1 Names] field apart from the number!
2. Use a ‘Custom Split’
Click on the [Pivot1 Names] field and select the ‘Custom Split…’ option. Use a space as the separator and set the split settings to ‘Last 1 fields’.This will create a new field called [Pivot1 Names – Split 1] which contains just the number as we’re splitting off everything after the last space in [Pivot1 Names]. Remove the original [Pivot1 Names] field after this.
After either of the previous options, rename the final field to [Ingredient Position].
Getting the average price for each ingredient
After preparing our [Ingredients] field we now have one row for every ingredient in every cocktail. All that’s left is to find a way to get the average [Cocktail Price] for each ingredient. To do this we can add a ‘Aggregate’ step with the following settings:- GROUP on the [Ingredients] field.
- AVG the [Cocktail Price] field.
This gives us the average price for each ingredient across the whole data set. Rename the newly aggregated field to [Avg Ingredient Price].
To attach this back onto our full data set we need to add a ‘Join’ step which joins our full data to our average ingredient price data. To create a join simply click and drag the Aggregate step of the right-hand side of the step containing the full data. You should see a ‘Join’ box appear, at which point release your mouse click.
By default this creates an ‘inner’ join and Tableau Prep attempts to automatically join related fields. In our case in correctly automatically joins on [Ingredient] = [Ingredient]! The final step is to simply remove one of the now duplicated [Ingredient] fields.
