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 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!
Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions. The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges. A New Year means we start afresh so January's challenges will be focused on beginners. We will use dif
Challenge By: Jenny Martin For week 2 of our beginner month, Data Source Bank has a requirement to construct International Bank Account Numbers (IBANs), even for Transactions taking place in the UK. We have all the information in separate fields, we just need to put it altogether in the following order: Inputs A list of the transactions, with information about the receiving bank account A lookup table for the SWIFT Bank Codes Requirements Input the data In the Transactions table, there is a Sort Code field which contains dashes. We need to remove these so just have a 6 digit string ( hint ) Use the SWIFT Bank Code lookup table to bring in additional information about the SWIFT code and Check Digits of the receiving bank account ( hint ) Add a field for the Country Code ( hint ) Hint: all these transactions take place in the UK so the Country Code should be GB Create the IBAN as above ( hint ) Hint: watch out for trying to combine sting fields with numeric fields - check data types Re
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in
