2021: Week 28 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week was to look at all of the results from penalty shootouts in the Football World Cup and European Championships and then analyse how good or bad the teams have been.
Step 1 - Input Data
The first task is to input both tables from the Excel document, one for the World Cup one for the Euro's.
These can be input via the wildcard union feature, but the field names don't quite match up directly, therefore I've decided to bring both tables in and then use a union tool.
As you can see some of the fields need to be merged and we can do this by using functionality in the union tool, by selecting a field and then choosing the + of the 2nd field that you want to be merged:
Step 2 - Clean Fields
The next step is to do some general cleaning of the competition, dates and teams. The following changes have been made:
Competition
To parse the competition from the Table Names field (this is created by the union), we can use the split functionality to return everything after the last '/'.
This removes all of the table information and leaves us with the competition name.
Dates
To calculate the correct date of each match we need to combine the Date and the Event Year fields. Currently, each of the date rows has a year of 2021, and this isn't correct for a lot of the matches, so we can solve this by using the following calculation:
MAKEDATE(INT(LEFT([Event Year],4)),MONTH([Date]),DAY([Date]))
This will correctly format our date with the correct field type and year value.
Teams
For some of the rows in our Team fields (winners & losers) there are some additionally leading and trailing spaces that we don't require. Using the Clean functionality within Tableau Prep we can easily remove these so that we have a nice clean list of teams.
After this cleaning our table now looks like this:
Step 3 - Combine Winners & Losers
The next step is to combine both the winners and losers takers into a single column. You could do this via a pivot (see other examples from posts on social media) or you could split into two branches, one for winners, one for losers.
I have gone for the split into branches approach, so now have two aggregate fields for both Winners & Losers.
Within the aggregation tool we want to return a single row for each of the Winning team's takers, therefore we just aggregate the following fields:
Then we can exclude any null values and create a 'Winner' string to show which branch the rows are from.
The same process can then be repeated for the Losers branch. Then we can union the results together to get a table with each of our takers in a single column:
Step 4 - Combine German Teams
At this stage we can group the West Germany & Germany teams by using a manual grouping feature. This can be done by selecting both of the teams and grouping them.
Step 5 - Win %
We are now in a position to start answering the questions from the requirements. First, we can calculate the win % for each of the teams.
To do this we want to aggregate how many times a team has won or lost a shootout by using the following aggregation tool setup:
From here we can then calculate the total amount of shootouts each team has been in by using a Fixed LOD calculation:
Round(([Shootouts]/[Total Shootouts])*100,0)
We then have our first output:
And finally we can rank these from highest to lowest:
Step 6 - Scored %
Next we are going to have a similar process but this time we want to calculate what the score % for each team is.
This time we need to calculate whether a penalty was scored or missed. This can be found in the Penalty Taker field with the following calculations:
Penalties Scored
IF CONTAINS([Penalty Taker],'scored') THEN 1
ELSE 0
END
Penalties Missed
IF CONTAINS([Penalty Taker],'missed') THEN 1
ELSE 0
END
From here we want to calculate the total penalties scored & missed by each team:
Then calculate the Scored %
ROUND(
[Penalties Scored]
/
([Penalties Missed]+[Penalties Scored])*100,0)
And rank the %s
We then have output number 2:
Step 7 - Penalty Position
The last part of the challenge is to see what the most successful penalty position number is. We can create another branch from where we calculated whether a penalty was scored or missed and total this for each penalty position number:
ROUND(([Penalties Scored] / [Total Penalties])*100,0)
And finally rank these %s as we have done previously:
The full output can be found 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!
