Preppin' Data

 Challenge by: Carl Allchin This week we have some messy data concerning our consultancy business, Preppin' Consultants. We need your help to clean up the data to help us do some analysis. The data has been captured by our sales team manually — yes, we should have put a system in place sooner. Can you help? Input One Excel workbook with three worksheets: Engagements - details of each consultant booking (not all engagements occurred) Initials - a list of letters to numbers  Grade - job grade for the consultants in the business Requirements Input all the worksheets in the Excel workbook Create an Initials field that has is formed as two letters. The Consultant Forename and Consultant Surname fields hold the details i.e. Carl Allchin in the data is 3,1 we need CA Create an engagement start date and an engagement end date The year is 2024 (in case you are doing this task after the normal release week) Clean up the Grade field by finding the minimum grade per person within the data se

Solution by Tom Prowse and you can download the workflow here . Step 1 - Home & Away The first step is to calculate how many points the home and away teams both scored using the following calculations:  Home Points if [Home Score] > [Away Score] then 3 elseif [Home Score] = [Away Score] then 1 else 0 end Away Points if [Away Score] > [Home Score] then 3 elseif [Home Score] = [Away Score] then 1 else 0 end From here we can then split the workflow into separate branches for the home and away teams. Within the Home branch we can remove the Away Points and Away Team fields, rename Home Score to Goals For, Away Score to Goals Against, Home Points to Points, and Home Team to Team. We can then calculate the goal difference:  Goal Difference [Goals For]-[Goals Against] We can then repeat this same process on a separate branch but this time for the Away team.  After repeating the process we should have two branches which we can union together so we have a single table: This is the sam

Created by Carl Allchin The final part of the Premier League results challenges involves trying to determine the teams' recent form. This is a common feature of sports tables so can you form with your data prep skills.  This challenge can involve reusing previous logic you've formed over the last two week's challenges. Learning to reuse logic is useful too! If you haven't done the previous weeks, you may want to do those two challenges before this one.  I'm going to give less clues as to the logic you need to follow this week as you're now familiar with the dataset and I want to see what you all come up with!  Input Like Week 36's challenge, let's use Week 35's Output as the data: Requirements Input the data Recreate the logic to form a data set of: Matchday Team Goals For (definition: goals scored by the team in the record) Goals Against (definition: goals conceded by the team in the record) Goal Difference (Goals For - Goals Against) Points Create

Solution by Tom Prowse and you can download the workflow here . Step 1 - Points per Game After inputting the data we want to identify how many points each team has. We can do this in two calculated fields:  Home Team   IF [Home Score] > [Away Score] THEN 3 ELSEIF [Home Score] = [Away Score] THEN 1 ELSE 0 END Away Team IF [Away Score] > [Home Score] THEN 3 ELSEIF [Home Score] = [Away Score] THEN 1 ELSE 0 END We can then split the workflow into two branches - one for Home, one for Away.  Within the Home branch we want to remove fields relating to the Away team (Away Points & Away Team) then rename each of the Home fields to the following:  Home Score --> Goals For Away Score --> Goals Against Home Points --> Points Home Team --> Team We can then calculate the goal difference for each match:  Goal Difference [Goals For]-[Goals Against] We can then repeat this the away side using a separate branch. Then we can bring both of these branches back together using a union s

Created by: Carl Allchin One of my favourite Preppin' Data challenges was created by Jonathan Allenby about forming the NBA standings from game results . This week's challenge takes the output of last week's challenge and forms the standings for the Premier League.  Input The input for this week's challenge is the output from last week's challenge: Requirements Input the data Determine how many points each team gets for each game Create a record for each game, for each team containing: Matchday Team Goals For (definition: goals scored by the team in the record) Goals Against (definition: goals conceded by the team in the record) Goal Difference (Goals For - Goals Against) Points Note there should be 38 records for each of the 20 teams in the league To form your league table for each matchday: Create a running total for each team in the order of the matchdays: Goals For Goals Against Goal Difference Points  Determine Position in the league for each team by matchday.

Solution by Tom Prowse and you can download the workflow here . Step 1 - Matchday Number The first task is to create a row number for each row of our data. This is nice and easy in Tableau Prep as it has been automatically created for us within the Input step. However, by default, this is a hidden field so we need to go into the input, right click and unhide that field. We can then create a Matchday number for each of the different weeks across the season. First we want to extract each of the matchday numbers from the Matchday 1 of 38 field using this calculation:  Matchday   TRIM(   IF CONTAINS([Matchday 1 of 38],'Matchday')    THEN RIGHT([Matchday 1 of 38],8)    END ) This will extract the 'n of 38' piece of the string, then we can split on a ' ' (space) to extract the first number from the string to give us the matchday:  Matchday   SPLIT([Matchday],' ',1) As a result the table should now look like this:  Because there is only 1 row per matchday wi

 Created by: Carl Allchin The football (soccer) season is upon us. The challenge this week is to take the game results and make them ready for analysis. Sadly, when you google Premier League results the data is not structured in an analytically friendly way.  We've taken a copy of all the results from last season, can you prepare it for analysis? Input One Excel file with a single worksheet: Requirements Input the dataset Add in a row number as at source (this will help for next week's challenge) Create a column to show which Matchday (ie which game in the 38 game season) each game occured in Remove any rows of the data set that don't contain game information It's helpful to put all the match information in one column rather than the two columns in the input Replace the new row character (\n) with a different character(s) (I use two pipe characters: ||)  \n is recognised as char(10) by Prep Builder Form separate columns for: Date Home Score Home Team Away Score Away Te