2024: Week 9 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Cancelled Flights
The first task this week is to remove any flights that have been cancelled. To do this we want to input the Customer Action table and from here we need to identify whether the customer has cancelled or not.
We can't just use a normal filter here, because we want to remove all rows related to the customer and flight if they have cancelled. Therefore, we can create a new clean step, then filter to keep only the 'Cancelled' values from the Action field and then keep only the Flight Number & Customer ID fields.
This provides us with a list of all the customer IDs and flight numbers where the customer has cancelled. We can then use this list to join back to our original table using an outer left (or right) join to remove these rows from the original table:
After the join we can remove any unneeded fields and the table should look like this:
Step 2 - Most Recent Action
Next we want to identify what the most recent action was for each of the customers on a flight. As there is a new row created every time there is an update, we want to filter to the latest for each customer and flight combination.
We can identify these by using an LOD calculation where we group by Flight Number and Customer ID then find the Max Date.
We can then use this date within a filter to return only the dates that are equal to the most recent action.
[Date]=[Most Recent Action]
At this stage our table should look like this:
Step 3 - Flight Details
The next part of the task is to identify how many seats have been booked as of that date on each flight. For this we can utilise the running sum calculation within Tableau Prep to add all of these together. First we need to create a field for how many seats each person has purchased, we are assuming 1 each, so just need a calculated field with the number 1 in it.
We can then use the running sum where we group by Flight Number, Class, & Date and sum the Number of Seats. This will give us a running total of how many seats have been booked as of the latest action date for each customer.
After calculating the number of seats booked we can include the Flight Details for each flight by joining the Flight Details table into the workflow by using an Inner Right (or Left) join on Flight Number & Class:
The resulting join means that there are some fields that need to be merged together in order to remove any null fields. We can do this by selecting both fields and using the Merge functionality. We want to merge Flight Number, Flight Date, and Class fields together with their corresponding fields.
We also want to tidy up the Date field by replacing any Null values with the latest date (as of the requirements).
Date
IFNULL([Date],MAKEDATE(2024,2,28))
At this stage our table should look like this:
Step 4 - Flight Capacity
Finally we can calculate the Capacity % for each flight. First we need to ensure that any null values in the 'Total seats booked over time' field have a 0 value instead as this will change the outcome of the % calculation. To do this we can select the Null value in the profile pane and replace it with a 0.
We can then calculate the Capacity % using the seats booked and capacity fields.
Capacity %
[Total Seats booked over time]
/
[Capacity]
Our final table 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!
