Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #212: Draft Masters

Highlighted
Alteryx Certified Partner

My Solution

Spoiler
212.png

Highlighted
Alteryx Partner

Just the first time to take part in this challenge, not sure if it correct, haha!

 

by the way, how should I add the spoiler?

Highlighted
Alteryx Partner

Like others on this post, I feel like this challenge kicked my butt.  I was able to get the final output to match, and I'm guessing it could have been done with fewer tools.  Still, I'm happy I completed this challenge.  It gave me exposure to some new data shaping techniques I hadn't encountered before and forced me to use some tools I don't use every day.

 

Good luck!

Spoiler
I broke this challenge into 3 parts:
1.) Reshape Draft Pick Values
2.) Clean up NFL Draft Picks
3.) Combine & Calculate

Part I: Reshape Draft Pick Values
This is the part that really got me.  I tried thinking of ways to use dynamic tools to do all reshaping in one step; like most things, I ended up having to balance time with practicality, so I ended up using a few tools to accomplish this.
Transpose - Get all of the data into 1 column
Formula - Now that all 14 column names are in 1 column, use a formula tool to standardize them (i.e, "Pick #" and "Value")
Multi-Row Formula - with the data being 100% vertical, use this tool to grab the pick number from the previous row
Filter - drop the rows that are no longer needed
Select - drop the unnecessary column

Part II: NFL Draft Picks
Union all 5 years of data, then change any Null grades to 0.

In the real world, I don't know that changing all Null grades to 0 makes sense.  I noticed a few players with Null grades that were picked in one of the first few rounds of the draft, meaning they were probably highly-sought prospects, so imputing the grade would have made more sense.  However, there wasn't a lot of information to impute from and the instructions weren't clear on how to approach this.  Without more information to go off of, assume "0" which ultimately matches the solution.

Part III: Combine & Calculate
From there, the solution is pretty straightforward.  Be sure to use a Union tool after the Join tool to keep all matches + any leftover data from Part II (i.e, drafts without a Draft Pick Value).  Use a formula tool (or Data Cleansing tool) to change any Null Draft Pick Values to 2 per the instructions. 

Sum up the grades and Draft Pick Values, divide, sort, sample, and browse!

mrmichae_0-1587473886896.png
Highlighted
7 - Meteor
Spoiler
using a standard Marco and merged at the end. 
freewind_0-1587482045316.png

Marco 

Alteryx Certified Partner

Here is my solution !

Highlighted
8 - Asteroid

Here is my solution

Highlighted
8 - Asteroid

After having submitted... I looked at the others and discovered a tool I had never used before... (which would have simplified my solution)!

 

Spoiler
Arrange!!!  Pretty Cool!  

I guess you learn something every day!

Highlighted
8 - Asteroid

Very nice task! Here is my solution. ; )

Highlighted
8 - Asteroid

Good one!  I hope we have football this year...

 

Spoiler
mj_solution.png
Highlighted
Alteryx
Alteryx

Love the arrange tool

 

Spoiler
AshleyL_0-1587495012213.png