2021: Week 27 - Solution
Solution by Tom Prowse and you can download the workflow here.
There are probably many ways of doing it, so if you have an alternative then please share it with us!
Step 1 - Join Seeds & Teams
The first step is to combine the Seeding & Teams tables. These can be joined together by using the Seed field on both sides:
After removing the extra Seed field our table will now look like this:
Step 2 - First Round Pick
We can now start to focus on each of the different rounds for which team has been selected. This is a similar process for each round so can be repeated for rounds 2, 3, & 4 as well.
The first step is to multiply the round number by 10 so that we remove the decimal and we can then join nicely with the scaffold table that was provided.
The join condition with the scaffold is going to be [1] >= Scaffold. This means it will fill in the amount of rows that is equal to the [1] field, and this will be used to help provide a better chance of being selected for the higher seeded teams. For example, the top 3 seeds all have 140 rows each (14%).
Now we have padded out the rows to match the chance of being selected, we can add a row number to each of these.
Row Number
{ORDERBY [1] DESC : ROW_NUMBER()}
The next step is to isolate a single row, so that we can create a random number to match with the row number that we have just created. To do this we can use an aggregate tool to find the max row number and then create the Random calculation based on this.
The random calculation is multiplied by the max row number and then rounded to a whole number.
Random
ROUND(RANDOM()*[Row Number],0)
Because the random() function creates a random number between 0-1 this will allow us to return a number between our min & max row number, which can then use to isolate our first pick. To return the selected team, we join the random number back to our table using the Row Number:
In this case the team selected is Oklahoma City so they will need to be removed from the remaining rounds of the draft. We can bring back all of the other teams by using an outer join where we exclude the selected team (Oklahoma City) from our original seed list - the step just before we multiplied [1] by 10.
Caution should be made when using Random() as we found there are some strange behaviours where updates sometimes don't appear in later parts of the workflow.
Step 3 - Repeat for Each Round
The same process for selecting the first round pick can now be repeated for rounds 2, 3, & 4 by replacing the [1] field with the corresponding round number. There currently isn't functionality within Tableau Prep to complete this in a loop so we will need to manually copy & paste then update the fields.
After repeating the process, we should have 4 separate steps with each of the round picks and the remaining teams who have not been picked.
For the 4 round picks, we can take each of those steps union them together so that they are in a single table. Make sure you have created a new calculated field, in each to step, to show which round they are from.
We should now have two tables:
1, each of the 4 teams picked.
2, the remaining teams
Step 4 - Bring Everything Together
The final step is to bring both of these tables together. We want to stack these onto top of each other using a union so we need them to have a similar field structure.
On the remaining teams table we want to rank the teams based on their original seeding so that they retain their draft rank as stated in the requirements. To do this we can use a rank calculation:
Then as there have already been 4 picks, we don't want this rank to start at 1, so we can add 4 to each value to realign the draft order with the 4 initial picks:
Selected Pick
[Selected Pick] + 4
Then after renaming and removing some fields our table should look like this:
The final step is to union the 4 round picks to this table and we should have our desired output! Note, this may look different due to the random function!
This was a tough one so if you solved it in a different way then we'd love to see how you did it!
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!
