Preppin' Data

Challenge by: Jenny Martin 2020 - what a year! Much has changed, few things have stayed the same. Even our star signs weren't safe! The introduction of a 13th star sign, Ophiuchus, threw things into disarray. Were you born on a day where your star sign has remained unchanged? Let's make a list of all those affected by the changes. Just a quick thank you for those who have stuck with us in 2020, those who joined us in 2020 and all who learnt something new about Prep!  Inputs Old Star Signs and Date Range  New Star Signs and Date Range  Handy Date Scaffold  Requirements Input the data . Be careful your data isn't mistaken for a header. Reshape and clean up the data so you have a column for the star sign, along with the start and end dates. Create a date range for the new star signs. Scaffold the data so you have a row for every date of the year (2020 is a good year to base this off, since it's even a leap year!) For the output, we're looking for a list of dates that h...

  Solution by Tom Prowse and you can download the full workflow here .  This week's challenge was from community member Kate Brown who created this workflow so that she could clean and prepare some data so that it was ready for her to make a viz that included Polygons. Let's take a look at how she solved it! Step 1 - Join Tables Our first task is to clean some of the field names from the US Open Winners table so that we have a better understand of what the fields refer to going forwards. We want to make the following field name changes:  Pos to Position player to Player to par to Total to Par round 1 to Round 1 round 2 to Round 2 round 3 to Round 3 round 4 to Round 4 total to Total  year to Year  Also, from the Location table, we don't need to include the Score or To par fields so these can be removed.  Once we have made these changes to both tables, we are ready to join these together using the following join conditions:  Step 2 - Calculate Par Now we...

  Challenge by Tom Prowse. This week's challenge comes from the winner of the Prepstar award at this year's Tableau Conference and member of the SportsVizSunday team - Kate Brown .  Kate recently created a viz all about the history of the USGA Women's US Open but before creating in Tableau Desktop she had to prepare the data in Tableau Prep to make this possible.  In the viz Kate has used Polygons to create a square for each round and each year. So for this week's challenge we are going to see what data prep is required to create polygons just like in the viz! Inputs  There are two inputs:  1, US Open Winners 2, Location Prize Money Requirements Input and Join both data tables. Calculate the total par score and round par score  for each year. The par score is the predetermined number of strokes that a golfer should require to complete a round. The tournament is made up of 4 rounds, with the lowest number of shots being the winner.  Next we need to crea...

Solution by Tom Prowse and you can download the full workflow here . This week the NBA is back so we took a look at how many hours each of the 30 teams will spend on planes this season flying around North America. Step 1 - Clean Matrix The first task this week is to convert the matrix into something that is a little more useful for us. To do this we want to take the Travel Time Matrix table and use a columns to rows pivot to bring all of the teams into a single column. The setup looks like this, where all of the teams are included in the pivot values:  After the pivot our data should now look like this:  Step 2 - Convert Travel Time Now we have converted the matrix into a more suitable structure, we can now start to clean the Travel Time field. We want to convert this into minutes, therefore we will need to remove any letters, and transform the hours into minutes.  1. Time Type Using this calculation we can identify whether the time field is made up of hours and mins or j...

Challenge by: Carl Allchin It's nearly here... we've all been waiting so long... I've been counting down the days... that's right the NBA season is finally back on 22nd December!  Due to Covid timings for this season are all over the place. The 82-game season has been cut to 72 games. This means there is a new structure for how the games are organised. I've simplified this structure a little (so as we're not here to Christmas): Own Conference - 1.5 games at home and 1.5 games away  Opposing Conference - 1 game at home and 1 game away The challenge this week is to work out how many hours each of the 30 NBA teams will spend on planes this season flying around North America. The timings have been taken from Google's directions where possible and has factored in private planes.  We want to know which team is: Travelling for the longest time Travelling for the shortest time Input Travel Time Matrix League Structure Requirements Input the data Make the matrix more...

Solution by Tom Prowse and you can download the full workflow here . Secret Santa isn't quite the same this year, with drawing names out of a hat not being COVID friendly! So the challenge this week is all about using Tableau Prep to assign partners and prepare the emails to be sent out to each person. Step 1 - Assign IDs The first step this week is to assign an ID to each person so that we can use this when pairing people up. We want this to be done in an alphabetical order, and can use a Rank calculation to do this (Row_Number will also work here):  Row Number Step 2 - Assign Matching ID Next we want to create another ID which will be used to create pairs. We are told that the next name alphabetically will match and also that the last name will match with the first name. Therefore, we first need to identify what the last ID number is by using a Fixed LOD:  Max Row Number We can now use the Max Row Number and Row Number fields to create a Matching ID field using this calculat...

Challenge By: Jenny Martin Secret Santa is a bit more difficult this year, without everyone being able to gather together and pick names out of a hat. Never fear though, we can randomly assign Secret Santas to their Secret Santees with the Power of Prep! Although we will simplify things a little so the assignments follow a logical rule rather than being completely random, but I'm sure our Secret Santas won't realise. After all, it's supposed to be secret! Input One input of the Secret Santa Participants and their email addresses:  Requirements Input the data Assign Secret Santas to Secret Santees that follow them in the alphabet i.e. Ellie should be the Secret Santa for Emma Since no one follows Tom alphabetically, his Secret Santee should be Ellie, as she is first alphabetically Some of the email addresses contain typos. Clean them up To make life easier, we're planning on automating sending the emails. Create a field for the Email Subject and a field for the Email Bod...

  Solution by Tom Prowse and you can download our  workflow here .  This week's challenge is all about the NBA and is inspired by Tableau Zen Master Rob Radburn  who created a great viz on Formula 1 data. We decided to take his idea and explore the 2018/19 NBA season so that we could then go on and create a similar visualisation! Step 1 - Input all of the data The first step this week is to input all of our data tables. We could do this individually, however this will take a long time and not be very dynamic. Therefore, we use the Wildcard Union in the input tool, and include all of the sheets from the file:  We then need to tidy up some of the fields by renaming and removing some of them. Our data should look something like this:  Step 2 - Who Won? The next step is to determine who won each of the games using the following calculations: H Win INT([H PTS] > [V PTS]) V Win INT([H PTS] > [V PTS]) Each of these calculations returns a 1 (win) or 0 (loss) ...