Intermediate Alteryx challenges normally take several different types of tools to complete. Challenge #210 required tools for parsing, joining, and transforming.
To start off we are given two files, the first contains data on each all star player from 2010 - 2016. The second has the number of picks per draft round per year. We need to use these two files to figure out what overall pick each all star player had and then find the three highest. The initial file has what year, round, and pick number they were but we need to combine these to find their overall pick. Our goal is to figure out how many picks occurred before the round that each player was drafted in and add that to their pick number to find their overall pick.
The first step was some basic data preparation, make sure the fields are in the correct format. We also needed to split the field containing draft year, round, and pick into separate columns to be able to use the data in that column for our calculations. Then we joined the second data set to the first based on the draft year.
Now that the data is joined we need pivot each column with rounds into one column.
Next, we need to filter out rows that are not necessary for each player. So we only keep draft rounds that happened before or during the round that they were drafted.
With a summarize tool we then find that total number of picks until the round the player got drafted. Lastly, we use a formula tool to add the total number of picks to each player's pick.
Now our calculations are complete. We just need to filter out unnecessary fields and rows to find the top three:
