2024: Week 20 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Combine Tables
Our first task is to combine both of the input tables so they are in a single table. When inputting both tables into the workflow, we need to update the field names within the Customer Spending table so that the Name & Name-1 fields become First Name & Last Name.
We can then join the tables together with an inner join on First Name and Last Name from each table.
After removing any fields that we no longer require the table should look like this:
Step 2 - Pivot Data
Next we want to change the shape of the data so that we have a row for each Online or In Person instead of columns.
We can pivot the data using a Columns to Rows pivot where we bring both the Online and In Person fields in the pivoted fields.
Then we want to keep only the rows where the pivoted value returns a Yes. This will keep only the fields where the customer spent in person or online. We can then remove the Pivot1 Values field and rename Pivot1 Names to Online or In Person.
Step 3 - Day of the Week
The final task is to rank the sales for each day of the week and then output a data set for each day.
First we need to identify the days of the week, so we can use the in-built functionality within Tableau Prep to convert the date to the day of the week and rename to Weekday:
Next we can use a Rank calculation to rank the days of the week based on the sales:
Finally we want to output just a single day of data. For this we want to calculate a parameter which contains all of the days of the week as a string:
We can then use this in a filter to keep only the selected day within the parameter: [Weekday]=[Parameters.Day of Week]
After this we are ready to output the data that should look like this (with Monday selected):
You can view the output here.
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!
