2020: Week 39 - Solution

Solution by Tom Prowse and you can download our solution here. 

This week we took a deeper look at Jonathan's latest viz which focussed on Pret a Manger's new deal in the UK. However, we have some slightly more complex orders therefore we will use Tableau Prep to take a deeper look to see if the deal is worth while. 

Step 1 - Restructure Orders

First we are going to focus on the Orders input, and restructuring the table so that we have a line for order for each person. Therefore the first step is to use a Pivot tool to bring each of the day's into a single column: 

We can use a Wildcard pivot on 'day' which will automatically bring all fields which contain that word. 

After the pivot our table looks like this: 

So now we want to clean this up a bit, therefore we can remove any null values from the Pivot1 Values, and then split each of orders into a separate column. We can split using a custom split by ',': 

We now have a separate column for each of our drinks:

Next we want to pivot the data back so that all our drink orders are in a single column. This is similar to what we did with the Days, but this time we can use the wildcard pivot on the word 'split': 

After this pivot we want to remove any null values, and then Trim any extra spaces around the newly created Orders Split field. Our table should now look like this: 

The final part to restructuring the orders table, is to aggregate so that there is a line for each order and person combination, and a count of how many times this occurs. The aggregate tool is setup like this: 

After renaming some of the fields, this creates our newly structured order table:

Step 2 - Restructure Price List

Next we are going to focus on the Price List input with a similar process to the orders table. 

Again, we first want to pivot the data but this time we need to pivot two values compared to just one. Therefore the first pivot values can be a wildcard on 'Price' and then the second pivot values can be the remaining fields. The setup will look like this: 

After the pivot we want to remove any null values from the Pivot 2 Values field, and then remove the word 'Price' from the Pivot Names. There are multiple ways of doing this but we have used a custom split: 

Note: You may need to trim some spaces as a result of the split.

 The last part of this step is to rename some fields and our table should now look like this: 

Step 3 - Join Orders to Drinks

Now we can join our Orders and Drinks tables together, however we need to be careful as some orders don't have the exact same name as on the menu. We can break this down into a few different steps: 

1. Inner Join - Order = Drinks

We can use an inner join to match up and rows where the Drinks and Orders match exactly. The join will be like this: 

2. Outer Left Join

This time we want to do an Outer Left Join where we include all of the orders that don't match up with the menu. The setup for the join looks like this: 

As a result of this join we have a table that contains all of the orders that need to be matched up with a drink on the price list: 

So after the join we need to identify what category each of the drinks falls into. Luckily the last word of each order identifies this, so we can use the split functionality off Smoothie or Tea:

From the same join we also need to create another branch which will be used to clean the category field so that we can have a clean join later in the workflow. 

Within the 2nd clean step we want to trim any extra spaces from the Category Split field, and then want to remove any 's' on the end of the category. We can identify these by using the following calculation: 

Category Split 1

IF RIGHT([Category - Split 1],1)="s"

THEN LEFT([Category - Split 1],LEN([Category - Split 1])-1)

ELSE [Category - Split 1]

END

After this calculation we are left with a nice clean table that looks like this: 

We can now join this back to our first branch, using an Inner Join on Order Split = Category Split:

The last step is to remove any fields that don't match up. To do this we can use a Contains calculation: 

CONTAINS([Order],[Drink])

Using this within a filter will mean we only keep the rows where the Order is within the Drink string. 

Finally, we want to bring all of the fields back together from both of the joins. Therefore we can use a union tool to stack these on top of one another, and our workflow looks like this: 

After removing any unwanted fields our table now looks like this: 

Step 4 - Is the Deal Worth it?

This is the final part of this week's challenge and is where we see if the deal is worth it for each person. First, we need to calculate how much each person has spent on each drink per month. We calculate this by: 

Spend

([Frequency]*[Price])*4

*Multiply by 4 as that's how many weeks in the month

We then use an aggregation tool to calculate the total spend per person:

Now we have the monthly savings, we can round this to two decimal places: 

Monthly Spend

ROUND([Monthly Spend],2)

Next we can calculate the potential savings: 

Potential Savings

ROUND([Monthly Spend]-20,2)

*Minus 20 as that's the cost of the deal

Finally we calculate if the deal is worth while:

Worthwhile?

[Potential Savings]>0

After this calculation then this is the challenge completed for this week with this output: 

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!