2024: Week 1 - Solution
Solution by Tom Prowse and you can download the workflow here.
This is the first challenge of 2024 and we are running a beginner month once again. These challenges will focus on getting started with Tableau Prep (or data prep in general) and help you start building those good habits to achieve your New Year's resolutions!
Let's see how we can solve this challenge.
Step 1 - Input data
As with all these challenges we need to start with inputting the source data into our workflow. This can be downloaded from the challenge requirements and then input into the workflow.
We will be working with a CSV so we need to choose a text file and then make sure the table has been brought into our workflow canvas as an input step. Once we have the input, we can create a new clean step by pressing on the + to the right on the input
Step 2 - Split Flight Details
Now we have the data into our workflow we can start to clean it up and create our outputs.
First, we want to split the Flight Details field into 6 separate fields that all contain information about different categories. To do this we can use a custom split on the Flight Details field, where the separator is '//'
We want to split all values using the '//' as a separator:
This will create 5 new fields each containing information about different topics. As we are looking for 6 different topics, we need to do another split on the Flight Details - Split 3 field to split apart the From & To locations. This time we can use an automatic split to create 2 new fields.
We can remove the original Flight Details and the Split 3 fields, then rename the fields accordingly so the table looks like this:
Step 3 - Correct Field Types
Next we want to ensure that all of the fields have the correct data type. This will help us further down the line when we come to use this in further analysis or visualising our data. It's good practice to ensure that all of the field types are correct as early as possible.
Changing a field type in Tableau Prep is super simple, all you need to do is select the icon in the top left of the profile pane and then choose the relevant type.
For example, in the Date field we can select the 'abc' then choose Date instead of String:
We can then do the same with the Price field, but this time we select Number (Decimal) instead.
Step 3 - Update Flow Card Values
Sometimes we want to update individual values within our data. In this case we want to update the values in the Flow Card? field from 1/0 to Yes/No.
There are a couple of ways you can do this. You could use an IF statement within a calculated field, but as there are only a couple of values it may be easier to directly select them from the Profile pane and update them individually.
All you need to do is first ensure that the field type has been updated to a String field, then double click on a value, and replace it with the appropriate text:
Step 4 - Output Tables
We can now create the output tables that are listed within the requirements the complete the challenge.First we want to split the workflow into two separate branches by using separate Clean steps. The first Clean is going to contain information about people who have the Flow Card by using a filter to Keep Only the values that are equal to Yes. We can filter the table by selecting the value within the Profile pane, then choosing 'Keep Only':
Once we have repeated this for the 'No' branch then we can add an Output step onto each of these branches to complete the challenge:
Output 1 - Flow Card Holders
Output 2 - Non Flow Card Holders
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 & @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!
