2021: Week 15 - Solution

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

The challenge this week was all about restaurant orders and was provided to us by Amalia García-Vellido Santías. There were lots of good fundamental data prep techniques in this one so it was good to pull a few of these together. 

Step 1 - Menu Table Structure

First we want to bring in the Menu table and we want to change the structure so that we can have a single column for each Type, Plate Name, ID, and Price. To do this we want to use a Columns to Rows pivot with the following setup: 

As a result our data should now look like this: 

From here we need to do some more tidying to get the individual columns that we require. First we want to make sure we have all of the IDs in a single column and mapped correctly for the right item: 

ID 

CASE [Type]

WHEN 'Pizza' THEN [Pizza ID]

WHEN 'Pasta' THEN [Pasta ID]

WHEN 'House Plates' THEN [House Plates ID]

END

Then we want to do the same for the Price: 

Price 

CASE [Type]

WHEN 'Pizza' THEN [Pizza Price]

WHEN 'Pasta' THEN [Pasta Price]

WHEN 'House Plates' THEN [House Plates Prices]

END

Finally we can remove any Null values and the extra columns so our table now looks like this: 

Step 2 - Orders Table Structure

Next we want to bring in the orders table and then split each order number into a separate column. We can do this by using a custom split on '-': 

This will give us the orders separated out, so we can remove the original Orders field and then pivot so we have them all in a single column. We have used a Wildcard Pivot on 'Order -' which automatically brings all of the split fields in: 

From here we can remove the Pivot Names field, and then Exclude any blank rows in the Order Split field. Then the final part of this step is to rename the Order Split to Order ID and make this a Whole Number: 

Step 3 - Join Tables

Now we have got both of our tables into a nicer structure, we can join these together to make a single table. Using an inner join the join conditions look like this: 

As a result of the join, both of our tables are joined together 

Step 4 - 50% Off on Mondays

To calculate the new price of the orders on a Monday, we first need to find out which day of the week each order was made. We can do this using the DATENAME() function: 

Weekday 

DATENAME('weekday',[Order Date])

This returns a Monday, Tuesday etc for each of our dates. From here we can then use the following calculation to re-calculate the price for our Monday deal: 

Price 

IF [Weekday] = 'Monday' THEN [Price]*0.5

ELSE [Price] 

END

Step 5 - Output 1

For the first output we want to calculate the total money for each day of the week. For this we can use an aggregation tool to group by Weekday and Sum the Price: 

Our first output looks like this: 

Step 6 - Output 2

The second output requires a little more work in order to calculate who our most active customer is. Going back to our Orders workflow step (before the join), we can use an aggregation tool to count how many orders each customer has made. As we aren't worried about the price, the Monday deal doesn't come into play here. 

The aggregation is set up by grouping the Customer Name and then Count of items: 

Using the number of items that each customer has purchased, we can then start to rank our customers in order to find the top one. 

First we want to use a fixed LOD to find the Max items:

We can then filter this by using the calculation: 

[Count Items] = [Max Items]

So this will only return the customers who have made the most orders and in our case this is our second output: 

You can download the full outputs here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @amaliagvs, @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!