2022: Week 16 - Solution

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

We're looking at restaurant orders this week and how we can change the structure of the input so that it's easier to understand for when the chefs come to cook up the food.

Step 1 - Input Data 

First up we need to input the data and get this in a nicer format so that we can combine the orders with the recipe lookup table. 

In it's current form, the table of orders is a hard one for Tableau Prep to understand because we can't associate what has been selected (🗸) with who made that selection. 

Therefore, we need to split the Orders table into 4 separate inputs (1 for each person) and in each of the inputs we only want 2 fields - the name and the selection.

4 input steps: 

Example of the fields retained from Jenny's input: 

Once we have input the 4 separate inputs, we can combine these using a union step and then merge the fields so that all of the names are in a single field and the selections are in another field. You can merge these fields by highlighting the chosen fields, then right-click and choose 'Merge Fields': 

After the fields are merged we can then rename the fields to Guest, Dish, and Selections. For the Guest field, we want to rename each of the 4 Table Names values to be the corresponding name from the input. This is a manual step, so double click on a value in the profile pane and update.

Our table should now look like this: 

Step 2 - Extract Course Name

We're now ready to combine the recipe lookup table with our orders. We can do this by using an outer join where Dish = Dish and we want to return all of the order fields.

Then we can extract the course from the Dish list by using the following calculation:

Course 

IF ISNULL([Recipe ID])

THEN [Dish]

END

Then we can use Tableau Prep's grouping functionality to group together the similar fields (eg, Starter & Starters), and as a result our table now looks like this:

Step 3 - Fill Down Course Name

Next we want to fill in the missing values within the course field. As it mentions in the requirements, we want to change the recipe ID so that we can complete the fill-down technique. We can use the following calculation: 

Recipe ID

IF [Course]='Starters'

THEN 1

ELSEIF [Course]='Mains'

THEN 100

ELSEIF [Course]='Dessert'

THEN 300

ELSE [Recipe ID]

END

We can then use this to Order our rows based on their course:

Order 

The next task when completing the fill-down is to create a new branch and filter to exclude the null values in the Course field. We can then remove the Selections, Dish, and Recipe ID field and join this back to our previous step using Guest = Guest and Order >= Order:

At this stage our data has been duplicated based on the course, so we now need to ensure that we filter correctly to get back to the correct number of rows. 

First, we need to find the Maximum order number based on the rank field that we created previous. We need to do this for each Guest & Order combination (note, we need to find the maximum Order-1): 

Max Order 

Then using this field we can filter to only where the max is equal to the order-1 field - [Max Order] = [Order-1]. This calculation takes us back to the 52 rows that we had previously, but also retains the fill down in the Course field. 

From here we just need to do some tidying up before being ready to output the data. We can take the following steps: 

1. Remove Order, Order-1, Max Order fields

2. Rename Course-1 to Course

3. Filter where [Course] != [Dish]. This removes any fields that aren't actual dishes

4. Exclude nulls from Selections field. This retains only the selections that each person has chosen.

5. Remove Selections field.

After these changes our table should now to ready to output and 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!