This week continued with our Prep School theme by turning our focus to dates. For this challenge we want to buy a birthday cake for each of our students, therefore we need to use the same data set from last week to work out when to buy each one.
Step 1 - Input Data
The first step is to input the data. This is the same as last week where we are going to input the CSV file (text file input) into our workflow.
From the input step we can also remove any fields that aren't required for this week. All of the parental fields can be removed, therefore we can use the check boxes to easily remove these from the start of the workflow:
Step 2 - Pupil's Name
After inputting the data, we are going to touch on something that we covered last week; String Calculations. For this we want to do a simple calculation to bring the First and Last Name fields together:
Pupil's Name
[pupil first name]+' '+[pupil last name]
Remember we need to use the + symbol, inverted commas and a space to make sure the name is formatted correctly.
Step 3 - Pupil's Birthday
For the next task we want to create a date for the pupil's birthday this year (2022). To do this we need to extract the day and month from their birthday and then add 2022 as the year. We can do this by using a Makedate function:
This Year's Birthday
MAKEDATE(2022,
MONTH([Date of Birth]),
DAY([Date of Birth]))
This Makedate function is structure in a MAKEDATE(Year, Month, Day) format so we have utilised the Month() and Day() functions to create and format the required date.
Step 4 - Cake Day
Now we have created a date for this year's birthday, we now need to calculate on what date we require a cake. The pupils are in from Monday to Friday, therefore if their birthday falls on a weekend then we will get them a cake on the Friday instead.
First we need to find out what weekday each birthday falls on. Therefore, we can duplicate the This Year's Birthday field, and then use the in-built convert date functionality within Tableau Prep to convert to the Day of Week.
Next we need to realign any Saturday or Sunday dates so that they are a Friday instead. We can do this using a Dateadd function:
Cake Needed Date
IF [Birthday Weekday] = 'Sunday' THEN DATE(DATEADD('day',-2,[This Year's Birthday]))
ELSEIF [Birthday Weekday] = 'Saturday' THEN DATE(DATEADD('day',-1,[This Year's Birthday]))
ELSE [This Year's Birthday]
END
We also need to make sure that no dates have dropped back into 2021 after realigning for weekends. Therefore we need to do a second calculation to determine this:
Cake Needed Date
IF YEAR([Cake Needed Date]) = 2021 THEN DATE(DATEADD('week',52,[Cake Needed Date]))
ELSE [Cake Needed Date]
END
These two calculations should now give us the correct date for when each of the pupil's should receive a cake.
The last part here is to calculate the weekday and month based on when the cake is required. We again can use the Convert Dates functionality to convert the Cake Needed Date to 'Day of Week' and also 'Month Name'. Make sure you duplicate the Cake Needed Date first!
After making both of these changes our table should look like this:
Step 5 - Birthday's per Weekday & Month
The final step this week is to calculate how many birthday's there are on each weekday and month. To do this we want to use a Fixed LOD calculation which will help use to maintain each of the individual rows.
You might be familiar with Fixed LODs from Tableau Desktop, and you can write them out within a calculated field with the same syntax. However, Tableau Prep has a nice visual interface to help you understand what's going on a little better.
A Fixed LOD can be created from the same menu as a normal calculated field, but instead of the traditional calculation we get a nice visual analytical calculation setup instead.
From here, we can select what which want to group by (split the calculation up by) and also what we want to compute (what's being calculated). In our case we want to select Cake Needed On and Month in the Group By section and then we want to CountD of ID in the compute section.
After this calculation we can then remove any fields that are no longer required, and we are ready to output our data:
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
