2021: Week 16 - Solution
Solution by Tom Prowse and you can download our workflow here.
This week we looked at what would happen to the current English Premier League football table if the 'Big 6' teams were removed and didn't play any games. We wanted to create the current table and also the updated table, so there was a bit of repetition where we could leverage some of Tableau Prep's handy features.
Step 1 - Calculate Home & Away Points
The first step is to clean the Result field so that we see how many goals each team had scored in each match. There are some games that haven't happened yet so we can remove these by excluding the null values from the Result field.
Next, we split the Result field so that we have a field for home goals and a separate field for away goals. An automatic split should work here, but you can create a custom split using '-' as the separator. After renaming the fields our table should now look like this:
Now the home and away goals are separated, we can calculate the Home Points and Away Points:
Home Points
IF [Home Goals]>[Away Goals] THEN 3
ELSEIF [Home Goals]=[Away Goals] THEN 1
ELSEIF [Home Goals]<[Away Goals] THEN 0
END
Away Points
IF [Home Goals]<[Away Goals] THEN 3
ELSEIF [Home Goals]=[Away Goals] THEN 1
ELSEIF [Home Goals]>[Away Goals] THEN 0
END
The next step is to split the workflow into two different branches - Home & Away. We do this by using two separate aggregation tools to calculate the number of rows, goals scored, goals conceded, and points won for each home or away team. The aggregation setup looks like this:
Home Branch
Away Branch
We've renamed the Home Goals & Away Goals fields to be Home/ Goals Scored/Conceded depending on the branch that you are working with. Then also renamed the Number of Rows to be Home/Away Games Played.
The next step is to combine both of these branches so that we have all of the teams in a single table. We can join these on Team = Away Team so we have a single row for each team with their home & away results in a single row.
Our workflow now looks like this:
And our table should now looks like this:
Step 2 - Calculate Total Points & Goal Difference
Now we have all of the home and away data in a single row, we can start to calculate the Total Points and Goal Difference for each team. We need to use the following calculations to create the necessary fields for our table:
Total Points
[Home Points]+[Away Points]
Total Goals Scored
[Home Goals Scored]+[Away Goals Scored]
Total Goals Conceded
[Away Goals Conceded]+[Home Goals Conceded]
Goal Difference
[Total Goals Scored]-[Total Goals Conceded]
Total Games Played
[Home Games Played]+[Away Games Played]
Step 3 - Create Current League Table
As a result of all of these calculations we can now remove some fields so our table looks like this:
Then the final field we need to create is the position. There are a couple of ways to do this including using the Row_Number function, but we are going to use the Rank function with the following setup:
Notice how we have ranked by Total Points (desc) first and then Goal Difference (desc), this will allow for the correct sorting in case of a tie on Total Points.
As a result we now have our first output - the current league table:
Step 4 - Updated Table
The scenario within the challenge is to find out what would happen if the 'Big 6' weren't involved in the Premier League this season, and then to recalculate the league table. For this scenario, we want to go right back to the start and create a new branch from the original input.
From the new clean step we want to exclude the 'Big 6' teams from the league. There are multiple ways of doing this, but we have gone for a manual Filter by Selected Values for each of the Home Team & Away Team fields:
Once we have removed the teams, we then want to complete exactly the same steps as we used for the current league table. We could make each step again, however Tableau Prep allows you to 'Reuse' steps and save them as a flow.
Therefore we want to highlight all of the previous steps, from splitting the result field to calculating the league position and save these as a flow. I've grouped these steps in the image below:
We can then add this saved flow after our step where we removed the 6 teams and this will automatically calculate the league table with our updated teams:
You can also group fields so that workflow looks a bit cleaner and easier to understand. For example I have grouped the Current and Updated table steps so that it is clear what steps belong to each and reduces to number of steps on show:
Step 5 - Position Change
The final step this week is to see how each team has changed position in the newly updated table. To do this we want to join the current and updated tables together using Team = Team. You may want to remove some unneeded fields from before the steps before the join to make this easier to handle after the join.
We can then calculate the position change for each team:
Position Change
[C_Position]-[Position]
Then finally we can remove the Positions and extra team fields before recalculating the position again so that the teams are in descending order:
As a result we have our Updated Table output:
You can download the full outputs here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
