Preppin' Data

 Created by: Carl Allchin Last week's challenge involved cleaning up the consulting engagements to ensure we didn't have any overlapping engagements. This week's challenge involves conducting some analysis on the engagements. We want to understand who our top earners are at each grade and for the organisation.  Input One excel file (the output from last week's challenge) Requirements Input the data Create a row for each day a consultant is on the engagement Remove weekend days  Work out how many calendar days occur in each engagement (incl. weekend days) Aggregate the data to: Count the number of calendar days a constant is on engagements for The total earned by each individual per engagement Retain the engagement number, initials and grade Rank the consultants by day rate earned, per engagement: Overall rank  Grade rank Output the data Output 7 data fields: Calendar Days Initials Engagement Order Grade Name Day Rate Overall Rank Grade Rank 718 rows (719 incl. heade...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Initials Field First we want to add the Engagements and the Initials data source into the workflow. From here we can join these together using an inner join on Consultant Forename and Initial ID.  We can then remove the Initial ID field and rename the Initial to Initial Forename. Then we can create another join but this time it will be for the Consultant Surname and Initial ID.  After removing and renaming the fields, we can create an Initials field using the Forename and Surname:  Initials [Initial Forename]+[Initial Surname] Out table should now look like this:  Step 2 - Engagement Dates & Grades Next we want to create dates for when the engagement started and ended. We can do this with the Makedate function:  Engagement Start Date MAKEDATE(2024,[Engagement Start Month],[Engagement Start Day]) Engagement End Date MAKEDATE(2024,[Engagement End Month], [Engagement End Day]) We can then ...

 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 da...

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: T...

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 ...

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 (3 points for a win, 1 point for a draw and 0 points for a loss) 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 Po...

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 ro...