2020: Week 16 - Solution
You can find our full solution workflow below and download it from the Preppin' Data Community Forum page!
Step 1 - Input Files
Our first step is to input both of the tables that contain sales from our Lewisham and Camden stores. We can do this by using Tableau Prep's wildcard union feature. Within the setup we are going to include all sheets with the matching pattern '*Sales'. This looks like this:
This will automatically pick up any additionally stores added in the future, as long as they end with 'Sales'.
Step 2 - Clean Sales & Profit
The next step is to clean fields, so that we are able to create one column for Sales and one column for Profit.
The first task is to tidy a couple of columns after the wildcard union. We can remove the 'File Paths' field, we are going to remove the word 'store' after Camden/Lewisham. We can do this easily by using the Custom Split functionality, and splitting off the First values before the ' ' (space).
Our split looks like this:
The first task is to tidy a couple of columns after the wildcard union. We can remove the 'File Paths' field, we are going to remove the word 'store' after Camden/Lewisham. We can do this easily by using the Custom Split functionality, and splitting off the First values before the ' ' (space).
Our split looks like this:
We can now rename this field to 'Store' and remove our 'Table Names' field.
Now we are ready to Pivot our data so that we can get a singular column for both Profit and Sales. We need to split this off into two different branches and use two pivot tools.
Profit Branch
We are going use a Columns to Rows pivot, and bring in all of the columns that contain data relating to profits. A quick way of doing this is using the Wildcard Search, where we can just write 'profit' and this returns all of the fields with the word 'profit' within the header.
This is a good trick for future-proofing your solutions, as if/when columns are added in future months, then Tableau Prep will automatically add these to your pivot.
We are going use a Columns to Rows pivot, and bring in all of the columns that contain data relating to profits. A quick way of doing this is using the Wildcard Search, where we can just write 'profit' and this returns all of the fields with the word 'profit' within the header.
This is a good trick for future-proofing your solutions, as if/when columns are added in future months, then Tableau Prep will automatically add these to your pivot.
Next we want to remove the sales field, then rename the 'Pivot Names' field to 'Date'. We also want to use the Clean functionality to Remove All Letters from the date field.
Sales Branch
For the Sales, we are going to repeat all of the steps that we did for Profit. We will take the following steps:
We now have a branch for both Profit and Sales, this is what our Flow looks like so far:
For the Sales, we are going to repeat all of the steps that we did for Profit. We will take the following steps:
- Pivot Tool - Use a Columns to Rows Pivot
- Wildcard Search - Search for all fields with 'Sales'
- Remove Profit - Remove all of the profit fields
- Clean Date - Rename 'Pivot Names' to Date, then clean by removing all letters.
We now have a branch for both Profit and Sales, this is what our Flow looks like so far:
The final step, to create a column for both our Sales and Profit, is to use a Join Tool to bring both of the branches back together. We join on Store, Date & Scent, and the setup looks like this:
Step 3 - Join Store Days Worked
The next part of the challenge is to join our Sales & Profit flow with the Store Days Worked table. We have all the fields needed to join, however there is some preparation needed beforehand in order to get both tables in the same format.
First, we want to pivot our three different stores so that they are all in one column.
Our pivot looks like this:
Our pivot looks like this:
Now all the stores are in one column, all we need to do is rename a couple of fields and we should be ready to join. We rename, the 'Pivot Names' to Store and 'Pivot Values' to Staff Worked Days.
Next, we need to prepare our original workflow, so that the dates are in the correct format. Currently, we have two different date formats in our two tables:
- Sales & Profit - 'dd/MM/yyyy'
- Staff Worked Days - 'MM/dd/yyyy'
Therefore, when we come to join these together the results will be incorrect. To ensure that we are joining the dates correctly, we need to make sure that both are date fields. The Staff Worked Hours table is correct already, as our Month field is already a date, but we need to make some changes to the Sales & Profit date as this is a string field.
To ensure we get the correct format, we need to use a DATEPARSE function and the following calculation:
Date
DATE(DATEPARSE('dd/MM/yyyy',[Date]))
We now have two dates in the correct format, so we can now join the tables together correctly.
The join is setup as an inner join, joining on Store to Store and Date to Month:
Notice how 4 records are excluded from the join, and these are for Dulwich store hours worked because we do not our Dulwich store in the Sales & Profit data.
The final step is to remove any fields that are not included in the Output, then use an Output tool to export to a file type of your choice.
Our output looks like this:
Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!
