This week was the final challenge of 2021 and also the last instalment of our Departmental December! We focused on the Operations team this week and how we can help the Prep Air team improve by understanding their customers' complaints.
Step 1 - Complaints per Customer
First up we want to calculate the number of complaints each customer has made. We can do this by inputting the Complaints table and then using a Fixed LOD to calculate this whilst maintaining the same number of rows.
For each Name we want to do a distinct count of their complaint:
Complaints per Person
As a result of this we should have numbers ranging from 1 complaint to 3 complaints.
Step 2 - Join Department Responsible Table
The next step is to combine our Department Responsible table with the Complaints table in our workflow. The problem here is that we want to join on a keyword, but that keyword is within the sentence of the complaint.
To overcome this, we need to perform a cross join, so that all of the departments and joined to all of the complaints, and from there we can see if the complaint contains one of the key words.
First we want to do the cross join between the tables. For this we need to use a calculation to create a field within both tables that can be used as this join. We have decided to use a 1, but you can use anything as long as it's the same in both tables.
From here we can remove all the created fields containing 1 as we only required these for the cross join.
Next, we can focus on whether the field contains the keyword from each department. Before we filter our results we first need to make sure the Keyword and Complaint fields are in Lowercase. We can use the Clean functionality in Prep to do this easily.
We can now start to filter the extra rows that were created as a result of the cross join by using a calculation within a filter to determine whether or not the complaint contains the keyword:
CONTAINS([Complaint],[Keyword])
At this stage our data should look like this with only the complaints that contain a keyword a the corresponding Department:
Step 3 - Comma Separated Field
Now we have given each of the complaints a department, we now need to combine any keywords that appear for each complaint and department combination. By combining these keywords with a comma then we will eliminate any duplicates or multiple rows of the same complaint.
First, we need to pivot our data so that each keyword is it's own field with the keyword in the rows as well. To do this we need to duplicate the Keyword field, and then use this within a Rows to Columns pivot where the Keyword is in the Pivoted Fields and a min of Keyword-1 is in the Aggregated section.
As a result of the pivot we should have each of the keywords as a field with a null or the keyword in each of the rows:
The next step is to create the concatenated, comma separated field for each of the keywords. There isn't a nice feature within Tableau Prep to do this but we can use the following calculation to create the concatenated list:
After this calculation it will create a comma before each of the keywords (even if they don't need a comma), therefore we need to remove these additional commas.
Complaint Causes IF LEFT([Complaint causes],2) = ', ' THEN MID([Complaint causes],3) ELSE [Complaint causes] END
As a result of this calculation we have removed the extra commas, and then only returned the keywords as required. After removing all of the keyword fields our table should look like this:
Step 4 - Unknown or Other Complaints
The final task of this challenge is to categorise any complaint that doesn't have a keyword. To do this we need to include join back to our original complaints table (where we calculated complaints per customer in step 1) so that we can filter the complaints list so that we only have the ones without a keyword/department.
We can use the join conditions to create this filter by using an outer right (or left depending on your setup) join where Name = Name and Complaint = Complaint:
After removing and renaming some of the fields our table should look like this:
Notice how the Department and Complaint Causes fields are null, therefore we can replace the nulls with Unknown and Other in the corresponding fields.
Now there aren't any nulls within these fields we can combine them back with our other complaints so that we have the full list that we started with. Using a union tool we can bring back the two tables together by 'stacking' them on top of one another:
The final task is to remove the Table Names field 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
