Challenge #125: US Grand Prix Lap 1 - Data Prep

I'm excited to present to you Lap 1 of the 2018 US Grand Prix! If you were at Inspire in Anaheim with us in June, you saw our 3 finalists (@BenMoss, @mattagee, @NicoleJohnson) solve this workflow in front of roughly 3000 Alteryx users and fans.

Tackling this workflow won't compare to the experience our finalists had, but it will give you a glimpse into what it takes to compete in the Grand Prix. To make it more life-like, I suggest doing the following.

1) Open up the workflow and give yourself 2 minutes to read the prompt - don't touch the mouse!

2) Time yourself - the drivers had a maximum of 12 minutes to finish.*

3) To take it to the next level, invite your friends and neighbors over, provide them food and drinks, and put on some headphones as you solve.

*At Inspire, the journey did begin by the drivers having to find the data in Alteryx Connect - I've provided the data for you in the workflow.

challenge_125_start_file.yxmd

Join

Preparation

Advanced

Data Investigation

Data Preparation

Basic

Accepted answers

JimmyG

Here is the answer I showed at Inspire.

Great job, everyone, It's awesome to see so many people timing themselves!

challenge_125_end_file.yxmd

All comments

Kenda

Off to a running start!

Spoiler

Had never used the Field Summary tool before. Definitely would have felt the pressure in front of 3,00 people!

challenge_125_BarnesK.yxmd

LordNeilLord

Took about 5 mins, so I spent the rest of the checking that all of my values matched....but there are some discrepancies!

I suspect the answer file came from a slightly different source

Spoiler

challenge_125_LNL.yxmd

AGoyal

Here is my solution.

challenge_125_start_file.yxmd

jasperlch

Solution attached. Thanks!

challenge_125_JL.yxmd

NicoleJohnson

Well, let me just say that while this round was FAR less stressful than the last one, it was not nearly as fun...

Spoiler

Finished this one for the second time in just about 3 minutes - about half the time, and from what I can recall, about half the tools! But then I spent another 3 or so minutes trying to figure out why my answers didn't match when I used the Expect Equal tool... Upon further investigation, I suspect that the solution provided removed the decimal places in the Pay Rate field (turning $14.00 into $1400, etc.), which is resulting in the difference in min/max data for the Pay Rate field. Otherwise, looks like a match (and based on the requirements of the Join, was technically a match for the requirements of the challenge despite the difference in the Pay Rate field).

Nice to get a chance to relive this one in a less intense environment haha! But I would still go back and take that on-stage adrenaline rush any day...

Cheers! (Now, to go study up again on what on earth went wrong in Round Two, just in case that one is next in the pipeline...)

challenge_125_NicoleJohnson.yxmd

Ladarthure

Great Challenge!

challenge_125_start_file.yxmd

KevinW

I love my formulas (and to be honest, I forgot about the datetime tool)! ah well, it saved me one tool in the workflow.

Spoiler

challenge_125_solution.yxmd

patrick_digan

Spoiler

1) Perhaps I'm missing something, but don't we need to address the duplicate employee id's before we join? It seems like 1204033041 is listed twice in both files. I sorted the data and then used a multi-row formula tool to add a grouped by record ID.
2) I don't think we have the exact same starting dataset as some of the values in the answer don't match the values coming from my solution.

challenge_125_start_file.yxmd

svenk

My Solution!

Challenge_125_SVSolution.yxmd

terry10

My 15th weekly challenge since I started in May. Made it to Camp 1; next stop Western CWM!

Spoiler

I decided to practice using parsing tools instead of a formula tool and to use embedded selects instead of separate select tools.

challenge_125_terry10.yxmd

Siddharth

My first Challenge

Spoiler

Siddharth_challenge_125.yxmd

KOBoyle

Solution attached. It took me a little longer than 12 min. I didn't look closely at the Pay Rate until I saw my results weren't matching up.

challenge_125_solution_KO.yxmd

Nico

Submitting my first weekly challenge. Interesting to see the different approaches!

challenge_125.yxmd

Ukashi

Nice challenge - managed to fit under 12 minutes

Spoiler

challenge_125_Ukashi solution.yxmd

Krit_Gable

challenge_125_kp.yxmd

clant

Here you go, not sure if i took the right approach as I parsed the pay rate. It took about 10 mins to complete.

Spoiler

challenge_125_start_file_CL_GG.yxzp

danrh

Spoiler

5:22 - Not too bad!

solution.yxmd

StevenBetts

Here's my solution. Took just over 5 mins

Spoiler

challenge_125_start_file_SMB.yxmd

brgilbert

My solution

Spoiler

challenge_125_solution.yxmd

Brente123

Solution to challenge 125.

Spoiler

neilgallen

This is one way, I guess.

challenge_125_NGA Solution.yxmd

jvansistine

Took more than 5 minutes, but definitely less than 12. I confess that no one was watching or yelling at me. :-)

challenge_125_solution_jvs.yxmd

petgrap

challenge_125_start_file - PP.yxmd

Rachel_B

7 minutes. Will have to work on my speed if I want to win the Grand Prix

challenge_125_RachelB_solution.yxmd

philipmannering

Took me half an hour... oh well, I won't be entering the Grand Prix anytime soon.

Spoiler

ggruccio

Was close to 12 minutes but....then I went over because...

Spoiler

I spent way too much time trying to clean up the duplicate employee number when there was nothing else to link to in either file....both employees started before the term dates so I could not say based on date logic this person termed then the other person hired on....

All of this was totally unnecessary! The output based on the field summary didn't care that the numbers were duplicated. Oh well live and learn!

challenge_125_gg_finish.yxmd

mmongeon

Fun! Here's my submission. I was able to do it in under 12 minutes.

Spoiler

challenge_125_mongeon_complete.yxmd

TylerNa

The faster I try to go the more tools I end up using. Still managed to be under 12 minutes though.

WC125 GP Round 1 TN.yxmd

javila

My solution attached! Took maybe 10min. That leading/trailing white space will getcha.

challenge_125_solution_grandprix1.yxmd

PeterF

challenge_125_start_filePF.yxzp

Ruud

Spoiler

My solution. Took me about 3 hours.

JimmyG

Looking at most of these solutions that say they have slight discrepancies... Most of these match if you join on the 3 fields listed which would count as correct!

cbrettlockard

First attempt didn't quite work, and then I got distracted with something else and had to come back to this. So I didn't time myself, but I got there.

Spoiler

challenge_125_CBL_solution.yxmd

tcroberts

Here's my solution:

Spoiler

Pretty simple workflow, although I did panic a bit near the end because I had never used the Field Summary tool and momentarily forgot that the instructions told us it would make the necessary output from the transformed data.

Really enjoyed the time limit for this one, it made it feel just that more competitive.

challenge_125_alteryxgrandprixlap1.yxmd

SeanAdams

OK - so this was interesting.

attempt 1 took about 5 mins with 10 mins spent trying to unpick a strange error message about named pipes.

Turns out that this is usually an issue with an earlier tool - and this has since been submitted as a defect to the support team. Version 1 of solution (attached) can replicate this outcome.

Attempt 2 took 5 mins again - different tools, and it worked.

(:-) either way - I would have struggled to compete with @BenMoss @mattagee and @NicoleJohnson on this since it took about 25 mins all-in, including the support ticket etc)

challenge_125_SeanAdamsSolutionV1.yxmd

challenge_125_SeanAdamsSolutionV2.yxmd

JORGE4900

Hey there @patrick_digan Nice catch about the duplicate records; I could see there was a duplicate Employee name by glancing at the interactive results for the employee name.

Looking at the records a little further, I could see there were two similar names, but the employee records was listed twice on both files as you point out.