2020: Week 44 - Solution
Solution by Tom Prowse and you can download the full workflow here.
Halloween has just passed so this week we were looking at costume sales across various different countries. This was a click-only challenge, so could you complete it using just the native functionality within Tableau Prep?
Step 1 - Group Costume Names
The first step this week is to input the data, and then we are going to focus on cleaning the costume names from the various different languages. We are going to use the grouping functionality to do this, and first we will group by Pronunciation.
Within the setup, we have changed the sensitivity of the grouping to be as high as possible. As a result, this catches a lot of the different languages, however there are still a couple that we need to go through and manually group ourselves.
We manually group by selecting the from the selection menu on the right-hand side:
We should now have 9 different categories for costumes, and they should all be in the English spelling
Step 2 - Group Countries
Next up we want to focus on cleaning our country list. In this list there are a few incorrect spellings, but remember we can't use any calculations to overcome this!
First, we want to use the data roles to transform the country from a String to a Geographic - Country/Region.
As a result of this, we now have some values with a red ! before their name. This is identifying the values that aren't a country because they have the incorrect spelling.
To overcome this and correct the spellings, we want to use the Tableau Prep recommendations feature. This is shown with the lightbulb icon, and gives us a couple of different suggestions:
Option 1 is to group and replace the incorrect spellings. Tableau will try to predict what the correct spelling is supposed to be, therefore correcting this without having to type any calculations.
Option 2 is to filter these values from our data set. At this stage, we don't want to do this, but if there were some values which weren't countries then this could be an option.
We have selected option 1, and our country list now looks like this and notice how the ! have changed to a paperclip to indicate that they have been grouped:
Step 3 - Convert Dates
The next part of this challenge is to readjust our dates so that we are working to a financial year that starts in November.
First, we need to change the data type of our date field from a string to a date.
Once we have converted to a date field, we can now use the Convert Dates feature within Tableau Prep. From here we can select 'Custom Fiscal Year':
Then select 'Year Number' and 'November':
The final steps to this part is to use a filter to exclude the 2018 dates, and then rename the date field to Fiscal Year. We should now only have dates for 2019 & 2020.
Step 4 - Pivot Sales
Next we want to change the shape of our data so that all of the sales values are in a single column instead of split out into 3 separate ones.
To do this we will use a Columns to Rows pivot, using a Wildcard pivot on the word 'Sales'. This will automatically bring through all of the fields with the word 'Sales' in:
Our data now looks like this:
Step 5 - Clean Sales
Now we have all of our sales in a single column, we want to clean this so that it's useful going forwards.
First, we can now remove any nulls from the Sales at Price field.
Next, we need to classify what price the costume was sold at (Full, Half, or Quarter). We can use the Pivot Names field for this, and using an Automatic Split Tableau Prep will automatically parse the key words out for us.
We can now focus on cleaning the sales values and extracting the currencies. First, we want to use a Custom Split to split out any numbers. We can do this by using a space (' ') as a delimiter and returning only the last value:
We now have a separate field containing only the numbers, so we then change this from a String to a Whole Number and rename it to Sales.
Now we have extracted the sales values, we now need to identify which currency each of them are in. Using the Sales at Price field we are going to do the following cleaning:
1. Remove Numbers
2. Remove Punctuation
These two steps have allowed us to clean the string so that we are now left with just the currency of each of the sales.
Step 6 - Pivot Years
The final step of this week's challenge is to use another pivot to create a column for each years sales. Instead of using a Columns to Rows pivot like last time, we are going to use a Rows to Columns with the following setup:
Our data now looks like this and is ready for the output:
The full output can be downloaded here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14, @JonathanAllenby & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
