2022: Week 29 - Solution

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

The next instalment of our introductory month contained another key technique that is fundamental to data prep - Joins. The challenge we want to combine our targets data with our original data source so that we can expand our analysis. Let's see how to solve it!

Step 1 - Sales Input

The first step is to input both of the data sets into the workflow. Once we have connected to the Sales table, we want to extract the product type from the Product Name field. To do this we can use a custom split on the '-' to split the first value from it:

We can then rename this field to Product Type. 

After extracting the product type, we want to aggregate our table so we can see the total sales for each Product Type, Store Name, and Region combination:

At this stage we have prepared all we need to you and our table should look like this:

Step 2 - Targets Input

We now want to turn our attention to the targets input. Currently, we have a field for each of our stores and this is in a different shape to our sales data. In order to be able to join the tables, we need to transform our table so that each of the stores are in a single field. 

To do this transformation we can use the pivot step. Within the pivot step we want to do a columns to rows pivot, and then make sure each of our stores are in the pivoted field section: 

After the pivot we need to rename a couple of the fields so we have a Store and Target field, and then multiply the Target field by 1000 (the input notes that the figures are in "k's"): 

Step 3 - Title Case

We have now got to the stage where we need to ensure that the Store and Product fields are the same in both tables so that we can successfully join them together. In Tableau Prep, you can have a mix of cases but to make sure that we are joining correctly it's always good to have them in the same case. 

For this we have a couple of different options. The first (easier) option, is to make all of the fields Upper or Lower case using the Clean features within Tableau Prep. This will ensure that all of the fields join as expected, but it won't look visually pleasing to the user.

An alternative way of ensuring that all fields are in the same case is to make them into Title Case. Title case is (roughly) where the first letter of each word is capitalised and this looks much more visually appealing. It also matches the case of the Store in the Sales table, so this will mean that we only have to update one of the tables.

To change the Store field into title case we can use this calculation:

Store

IF CONTAINS([Store],' ') 

THEN LEFT([Store],1)+LOWER(MID([Store],2,FIND([Store],' ')-2))

+

' '

+

MID([Store],FIND([Store],' ')+1,1)+

LOWER(MID([Store],FIND([Store],' ')+2))

ELSE

LEFT([Store],1)+LOWER(MID([Store],2))

END

This calculation is a bit complicated at first, but when we start to break it down it becomes easier. 

The first condition is whether the Store contains a space or not. This will allow us to identify if we need to capitalise any other letters other than the first in the string. If it does contain a space, then we want to isolate the first letter and then make the rest of the word lower case (up until the space). 

Then we want to add a space (' '), and then identify the next letter after the space within the original Store field. To identify this we use the Mid and Find functions which will allow us to keep the first letter and make the remaining letters lower case. 

We also need to do a similar technique with the Product field, but this is a little easier. 

Product

LEFT([PRODUCT],1)+LOWER(MID([PRODUCT],2))

Again we are identifying the first letter and keeping it capitalised, then using the Mid function we are changing the remaining string to lower case. As there are only single words, we don't need to identify any spaces within this calculation.

After both of these calculations our Targets table should look like this and we're ready to join: 

Step 4 - Joining Tables

Now we have prepared both tables so they are in a similar format, we can now join these together so that we can use fields from both tables in our analysis.

By dragging one of the branches on top of another, we can create a join step. Within this step we want to create an inner join where Product Type = PRODUCT and Store Name = Store. We need to make sure that both of these conditions are in the join or else we will have a table that has more rows than expected.

After the join both tables should be side-by-side and our table has gotten wider. We can remove the Product Type & Store fields as these have been duplicated as part of the join. 

The final step is to calculate whether each product in each store is above the target value. We can calculate that with:

Beats Target?

[Sale Value]>[Target]

After this calculation we are ready to output and our table should look like this: 

You can download the full 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!