Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #212: Draft Masters

AkimasaKajitani
17 - Castor
17 - Castor

My Solution

Spoiler
212.png

ScottyLeung
5 - Atom

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?

mrmichae
6 - Meteoroid

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
freewind
7 - Meteor
Spoiler
using a standard Marco and merged at the end. 
freewind_0-1587482045316.png

Marco 

Jean-Balteryx
16 - Nebula
16 - Nebula

Here is my solution !

pasccout
8 - Asteroid

Here is my solution

pasccout
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!

dpencheva
8 - Asteroid

Very nice task! Here is my solution. ; )

MJ
8 - Asteroid

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

 

Spoiler
mj_solution.png
AshleyL
Alteryx
Alteryx

Love the arrange tool 🙂

 

Spoiler
AshleyL_0-1587495012213.png