Preppin' Data

Challenge by: Jenny Martin I recently found some time to dust off my dashboarding skills and build a dashboard celebrating Andy Murray's 200 Grand Slam match wins. I wanted to create a dataset that would allow me to compare him to 3 of the other members of the 200 club: Nadal, Federer and Djokovic. Tableau Prep made this task nice and simple so why not use the workflow for a Preppin' Data challenge! View on Tableau Public Inputs The data comes from tennis-data.co.uk with a file for each year (2000-2023) containing a row for each match played in that year.  Requirements Input the data Bring all the files together into 1 dataset We'll only be using 7 fields for this challenge: Tournament Date Series Round Winner Loser Comment Filter to Grand Slam matches only We are only concerned with matches where Murray, Nadal, Federer or Djokovic played so filter to only these matches Remember they could be in the Winner field or the Loser field Categorise these matches as Win or Loss fo...

Solution by Tom Prowse and you can download the workflow here . This challenge is a collaboration with the Workout Wednesday challenges so make sure you check out the Desktop challenge after completing the Prep stage. Step 1 - Reshape Table After inputting the table into our workflow, we first want to look at ensuring the SDG headings are in a single row with information about the colour indicator and the arrow. To do this we need to utilise the Pivot functionality and also the ability to put more than one field within the values section. For this pivot we want to do a Columns to Rows and bring in all the SDG headers (without a 1) and then the SDG headers with a 1 into the second values section. Note: you can add the second set of values by pressing the + in the top corner After the pivot the table should look like this: Step 2 - Update SDG Fields We now want to update each of the SDG value & trend fields so that they are more meaningful than the colour or arrow. First, we want to...

Challenge by: Lorna Brown Workout Wednesday Coach Lorna Brown brings us another challenge! The challenge comes from a Data School Consultant's question and Lorna needed to reshape the data a little first so turned to Tableau Prep of course. If you want to complete the Tableau Desktop part of the challenge head over to the WOW website. Input The data is concerning Sustainable Development Goals  (SDGs) and if we take a look at the original then we have some idea as to why it needs cleaning:  Requirements Input the Overview sheet You'll notice if you open this in Excel that the SDG headings are merged across 2 columns, the colour indicator and the arrow. Find a way to rename the fields containing the arrows so it's still clear which goals they're associated with Reshape the data so that for each Country there is a row per SDG, containing information about the colour indicator and the arrow on the same row Rename the colour indicator field to SDG Value Rename the arrive fi...

Solution by Tom Prowse and you can download the workflow here .  This week we are looking at how we can create sequences and orders within a table when there isn't a field that is already available.  Step 1 - Input Data First we want to input both tables from the given output. This contains orders for July & August and we want to union these together so that they're in a single table that looks like this:  We can then use the Table Names field to extract the month name from the string of text. We can utilise Tableau Prep's feature to use the automatic split which will bring the month name into a separate field.  Then we can add the year to the month using a string calculation:  Date [Month]+" 2023" Then we can update the field type to a date and our table should look like this:  Step 2 - Sequence ID Now we have a date we can now start to create the sequence by using a rank calculation. We need to create the date as a source row number is created for bot...

 Created by: Carl Allchin When data doesn't have a field to set the order in which the records are formed, some easy questions suddenly become a lot harder; this week we are facing that problem.  I'm trying to analyse some of the wholesale purchases of my books and see how the orders are changing over time - can you help? (this is fake data before my publisher starts to worry about commercial sensitivities). Input One Excel workbook but two worksheets of data. Requirements Input the data but add a Row ID Try not to type in order in Excel as this is part of the challenge Pull the two tables together Use the file names to create a date  Create a sequence ID for the first order through to the last Create a field to determine how the current order differed to the previous order Call this field 'Bigger Order?' Output the data Output 6 fields: Customer Book Date Sequence ID Quantity Bigger Order? 18 rows (19 incl. headers) You can download the  output from here . After y...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Match Order First we want to create a calculation to show the order that the matches occurred. To do this we can use a Rank calculation based on the Team, Player ID and Tournament Date:  The table should then look like this:  Step 2 - Running Total Next we want to calculate the number of games each player has been involved in. To do this we need to calculate a running total for each match. To create a running total within Tableau Prep, we first need to do a self-join where we join our original data onto itself using an inner join where Team = Team, Player ID = Player ID, and Match Order <= Match Order: After the join we can complete the running total by using an aggregate step where we group by Team, Player ID, Total Matches, Tournament Date, and Matches Played in Tournament, then sum the Matches Played in Tournament-1: Then finally, to calculate the experience that each player has at the start o...

Challenge by: Jenny Martin This challenge is inspired by a recent dataset from a Data School client project. It has been reimagined, of course, with the fake company Chin & Beard Suds Co.  As you know, C&BSCo has branches all over London. They like to get together every other Sunday for a 5-a-side football tournament. Every time a player plays in a match, their Total Matches number is overwritten and increased by 1. This has caused some issues for the football data nerds who want to do some analysis to see if players who have played more games will score more goals. So we'll need to help them retroactively work out how many matches a player would have played in before the start of each tournament.  Input Just the one input this week, detailing the tournaments each player has played in. Requirements Input the data To work out the Experience each player has going into the tournament, we need to know how many matches they played after that match and minus this number from...

Solution by Tom Prowse and you can download the workflow here . For our HR challenge this week we are looking at internal transfers and how we can identify how often it happens and where the HR team should prioritise.  Step 1 - Transfer Flag After inputting the data source we want to create a flag to identify whether an employee has transferred across to a different distribution centre. As per the requirements we first need to identify whether the employee transferred or left and returned. To get the number of months between the different distribution centres, first we need to lookup the distribution centre in the next month: dc_next_month   { PARTITION [employee_id]:{ ORDERBY [month_end_date] asc :LOOKUP([dc_nbr],1)}} We can then lookup the next month for each employee:  next_month   { PARTITION [employee_id]:{ ORDERBY [month_end_date] asc : LOOKUP([month_end_date],1)}} Then we can use both of these to identify whether the employee had been away for longer than 2 mo...