2020: Week 23 - Solution
You can find our full solution workflow below and download it from the Preppin' Data Community page!
For week 23 we took a look at Quiz results and how we can use Tableau Prep Builder to help us calculate how many correct answers each team answered correctly.
Step 1 - Pivot!
The first step this week is to change the shape of our data so we can have a row for each of the questions.
Participant Answers
First, we need to bring in the Participant Answers table and use a Columns to Rows pivot with each of the 'Rounds'. We can use a wildcard pivot on 'Round':
After this pivot we are left with a row for each Name pair for each round, so 6 rows for each round.
We can now use a Split function to split each of the answers into a separate column. Luckily, all answers are split with a ',' therefore we can just use the automatic split to break these apart:
Now we have a column for each answer, we can use another Pivot tool to bring these back together. This time it will be a Columns to Rows pivot on each of the 'Split' fields:
We now have a row for each answer in each round of the quiz. This means that we have gone from 6 rows at the start of the workflow to 300 after both pivots.
After the pivot there is some tidying up to do, so we need to following these steps from the Pivot Names field:
- Remove Letters
- Remove Punctuation
- Remove All Spaces
- Rename to Question Number
After these changes our table now looks like this:
 |
Correct Answers
We are going to follow a similar process for the Correct Answers table as we want to join this to our Participant Answers workflow.
First, we need to split out all of the answers so that they have their own column. We can again use the automatic split to do this:
Again we are going to use a Columns to Rows pivot, but this time the wildcard 'Split' will return the answers:
Finally, we are going to clean the Pivot Names field by doing the following:
- Remove Letters
- Remove Punctuation
- Remove All Spaces
- Rename to Question Number
A quicker way of creating these could be to use the 'Save Steps as Flow' from the previous branch and then insert this in here. To do this, all we need to do is select the steps that we want to 'Save':
You can then select the 'Insert Flow' option from the 'Correct Answers' data set, select the saved flow, and then the steps will be repeated for you!
Now we have the Participant Answers and Correct Answers in a more appropriate shape, we can join these together using the following join conditions:
After the join our data table looks something like this, where we have a row for each pair's answer and also the correct answer:
Step 2 - Calculate Correct Answers
Now we have joined our tables, it's time to focus on calculating whether the answers were correct or not. We want to assign a 1 if the answer was answered correctly, and a 0 if it was incorrect:
Correct Answer?
IF [Answers Split]=[Round Split] THEN 1else 0END
After assigning a 1 or 0 to each of these, we can tidy some of the fields by taking the following steps:
- Question Number - Type change to Number (Whole)
- Remove the following:
- Question Number -1
- Pivot1 Names
- Answers Split
- Question Number
- Round Split
- Round-1
- Answers
We should be left with 3 columns:
The next step is to again use a Pivot tool, but this time we are going to use a Rows to Columns to create a column for each round with the total score:
We now have a line for each pair of participants, with each of their scores in each round:
This has solved the part of the challenge related to the score for each pair, however we need to also calculate the total score overall. We could do this by adding each round individually, however this very manually and not great if there are lots of rounds.
Therefore, we can return to an earlier step and use an aggregation tool to calculate this instead:
From the step where we calculated the 'Correct Answer?' we can use the following aggregation step to group by Name and Sum Correct Answer?:
After the aggregation, we can then join this back to our original flow so that we now have the total correct score for each pair:
We use the following join condition:
The final step is to calculate the order in which the pair's finished. To do this we want to use the Ranking function within Tableau Prep Builder. We want to use a Rank Dense rank the Total Score, descending:
After the rank, we can tidy any wanted fields and we now have our output:
Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!
