Preppin' Data

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