Challenge #29: Alteryx '16 Grand Prix (Race1, L1)

The link to last week’s challenge (challenge #28) is HERE

Hi everyone, what an awesome time we had at the Alteryx Inspire 2016 Conference in San Diego last week. It was so nice to meet many of the Community members in person. If you attended the conference and went to the Grand Prix event, this week’s challenge is the first of the four laps. We will cover all of laps as weekly challenges in the next weeks. See if you have what it takes to compete, keep in mind the contestants only had about 10 minutes per lap maximum so maybe time yourself.

Use Case: You are the commissioner for your fantasy baseball team. You recently completed your draft and you want to run some simple statistics about each fantasy team.

You have 3 inputs

1) Fantasy pick summary from your draft

2) Hitter stats on all field players (whether selected in your draft or not)

3) Pitchers stats on all pitchers (whether selected in your draft or not)

Objective: In order to run some stats on your draft, you first need to prep your data. Please combine all 3 files so that you have a single output that contains stats on each player drafted in your draft, ordered by "Overall_Pick."

Drivers start your engines!

challenge_29_start_file.yxmd

challenge_29_solution.yxmd

Developer

Join

Preparation

Intermediate

Data Preparation

Core

Accepted answers

All comments

JohnJPS

Fun stuff. :-)

Spoiler

jdunkerley79

Fantastic hoped we'd get to try these

Took me about 12 mins but under no pressure (+ another couple to verify result fully).

Spoiler

charlescolemansm

This is a cool one. jdunkerley79, your solution is better and faster. Always like to throw out different options when I can:

Spoiler

JohnJPS

Added my spoiler above; very similar to @jdunkerley79, and took about the same time, including the verification joining to the expected output.

markp201

From the other posts, I get the impression I over-simplified but here it is. Appreciate any comments.

Spoiler

GeneR

@charlescolemansm, what were you calling with the run command tool?

GeneR

@markp201, nice! I always believe simple is better...

charlescolemansm

@GeneR, I'm just saving a temporary .csv, leaving 'first row contains field headers' unchecked, and then reading it again, this time with field headers checked (i.e. as a more normal .csv).

All that said, <spoiler alert> Dynamic Rename is totally the better way to do this

TaraM

And here is the solution from the team:

Spoiler

2016-06-20 09_20_59-Alteryx Designer x64 - Fantasy_Baseball_GP2016_Lap 1_Solution (002).yxmd_.png

(attached above)

markp201

Curious - is there a performance gain by having the two joins followed byt a union?

GeneR

@markp201,

There is not a performance gain, I was solving the problem as fast as I could at the time and was thinking that a Batter may also have pitching stats. I want to keep the two sides isolated then bring in the data with the union.

Since there are not any batters that are pitchers it could be solved with just two joins in series like the winner did.

I had no involvement in creating the exercise initally, else I would have wanted to fix the header typo and only have 27 instead of 28 columns in the result. (there is a column called Stike outs and one called Strike outs that could have been aligned)

Best Regards,

@GeneR

SeanAdams

Similar solution as those provide. Sadly, well over 10 mins spent (mainly trying to figure out differences between my answers and the provided solution) - so DNF for me...

Note for @GeneR / @TaraM. It seems that one field is missing from the data provided (both in the start file, and the provided solution also fails on this field) - "2015 Team Rank". It may be that this was supposed to be 4 data sets originally?

challenge_29_SeanSolution.yxmd

NicoleJohnson

Solution!

Spoiler

challenge_29_NicoleJohnson.yxmd

Garrett

Fun with data cleansing!

challenge_29_start_file_glodewyck.yxmd

estherb47

Love baseball. Fun challenge

Spoiler

challenge_29_EHB_solution.yxmd

LordNeilLord

I spent ages trying to figure out why the join wasn't working, then I realised it was because I didn't read the instructions properly...

Spoiler

challenge_29_LNL.yxmd

nick_ceneviva

Solution attached

challenge_29_Cenevia.yxmd

Laurap1228

Here is my solution. Haven't checked it with the answer provided but I imagine it's quite similar.

Spoiler

Here's my solution.

Spoiler

challenge_29_JL.yxmd

patrick_digan

Spoiler

challenge_29_start_file.yxmd

Natasha

My solution is pretty standard to the above, it took me the most time to to figure out that I have 1 less column not because my join is wrong, but because there is no data for 2015 Ranks.

Spoiler

challenge_29_Natasha.yxmd

930realm

Took me reviewing the comments to realize the data was missing a field.

challenge_29_SP.yxmd

JoshKushner

This was fun

challenge_29_start_file.yxmd

samN

Pretty much the same as the solution except used the data cleansing tool

challenge_29.yxmd

Waynemk

one more

challenge_29_waynek.yxmd

ggruccio

Fun to try out a grand prix challenge!

challenge_29_gg_finish.yxmd

philipmannering

Solution attached.

Spoiler

challenge_29 - Grand Prix 2016 R1 L1.yxzp

LandonG

Solution attached.

challenge_29_start_file.yxmd

jasperlch

Solution attached.

challenge_29_JL.yxmd

dsmdavid

On hindsight, should have moved the union to the front, but I originally had a join there to test for players that were both pitchers and batters (didn't know they were all different)...

Spoiler

solved#29.yxmd

Sajeel_Kershi

Column for 2015 Team Rank Missing from input data files

challenge_29_SK.yxmd

sabaker

solution attached

challenge_29_SB_solution.yxmd

JMB001

Hello,

I mean to comment on the solution given by Alteryx.

In the last Select tool it appears that the field (Missing) 2015 Team Rank field has been created and placed at the bottom. This field show up in the Output box that is provided by Alteryx. I was under the assumption that this input and browse tool data was the intended final result of doing the workflow properly. The solution provided by Alteryx does not include the (Missing) 2015 Team Rank field in it's final Sort Tool output and therefore does not match the Output tool container. What am I missing?

Thanks,

Joe

brette

good use of data cleansing formula and developer tools

challenge_29_BE.yxmd

Dhrish

challenge_29_start_file.yxmd

Quick Links

Unanswered
Community Events
Groups

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335