2024: Week 2 - Solution
Solution by Tom Prowse and you can download the workflow here.
We continued our analysis from the 2024: Week 1 challenge where our output from there has become the input for this week. This week we are looking at unions, aggregations, and reshaping our data.
Step 1 - Union inputs
Our first task this week is to input both of the input tables. We'll need to add a new connection for both of these and then bring them in as separate input steps. Once they are added to the workflow we can drag one just below the other so that the union box is highlighted, and therefore creating a union step.
When we union data, this 'stacks' the tables on top of each other. This works in this use case as there is a similar field structure in both tables so we want to keep the fields the same and make the table longer.
Step 2 - Convert Date
Next we want to convert the date field so that we can identify which Quarter it is from. Using the in-built functionality within Tableau Prep, this is nice and easy and all we need to do is select Convert Date --> Quarter Number
Then we can rename this field to 'Quarter'.
Step 3 - Aggregate Data
We need to see the data in various different aggregations based on the Median, Min, and Max. For this we can use an aggregation step, but first we need to duplicate the Price field two times so that we have 3 Price fields (one for each aggregation type).
Then we can add an aggregation step where we need to group by Flow Card?, Quarter, & Class, then bring through the Median, Min, and Max with each of the Price fields.
Note: You can change the aggregation by pressing on the aggregate label at the top of the profile pane.
After renaming the fields our table should now look like this:
Step 4 - Separate Aggregations
We can now split the workflow into 3 separate branches, one for each type of aggregation. Within each of the branches we want to only keep the corresponding aggregation so we have a branch for each.
Then for each of the branches we can create a pivot step where we use a Rows to Columns pivot to create a new column for each of the different options in the Class field and then sum the corresponding aggregation (Median, Min, or Max):
After repeating that for each of the aggregations we can combine them back together with a union so they are stacked on top of each other again.
Step 5 - Rename fields
The final step before the output is ready, is to rename the fields so they match the requirements. To do this we can double click on a field header in the Profile pane and then rename them to:
- Economy to First
- First Class to Economy
- Business Class to Premium
- Premium Economy to Business
Then we can remove the Table Names field and we are ready to output our data.
If you want to complete the bonus task and add a field to show which aggregation is which. You can either keep the Table Names field and rename the values to the corresponding aggregation. Or alternatively, you can add a string field to each of the steps before the pivot fields with a string of each aggregation.
As long as each of the fields are named the same then this will flow through our workflow and will give us the name of each aggregation.
The output should look like this:
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!
