cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Welcome to Alteryx Academy! We hope you enjoy the journey. Let us know how we can improve your learning experience by completing this survey.
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.

Challenge #85: How Did Tara Place in Competition?

Alteryx
Alteryx

Last week's solution can be found HERE!

 

A few week's ago @TaraM competed in the Gladiator Games - an event that tests a competitor's overall fitness capacity in different capacities like endurance,  strength and agility, intervals, and metabolic condition.

 

Tara (bib # 2421) raced against her friend Carisa (#3508) in all 4 events but was only accidentally scored for 3 by the time keepers and her overall rank suffered. In the Interval event, Tara knew her time was exactly 1 minute faster than her friend, Carisa.

 

If the place is determined by the lowest average position for all 4 events, what overall place should Tara be? What was her place in the interval event?

Anybody who does not have all four events should not be ranked.

 

tara.png

I didn't get the same solution as what was in the file, so I look forward to seeing what I did wrong!

Edit: Fun challenge.

 

Spoiler
I first eliminated anybody who didn't have all 4 events. Then I used a bunch of sort/multi-row tools to get the ranks. Then i took an average at the end. I got 11th place, while the file had 12th place listed. It's fun finding uses for macros, so I used a dynamic filter macro I've been working on.
Edit:  I'm interested see if there is a better way to do multiple ranks. I could have transposed and cross tabbed the data, but I assume there is a better way.

 

Alteryx
Alteryx

@patrick_digan - Nice catch! I went back to my solution and found an issue that had me putting Tara in 12th place.  I just updated the start file to have the correct placing.

nick_ceneviva
Alteryx Certified Partner

Solution is attached.  Like @patrick_digan I did not get the same place as the solution, so interested to see what I could have done differently!

gnans19
Alteryx Certified Partner

I am getting 14th rank.

 

 

Quasar
Quasar
Spoiler
In the attached, it handles ties by using the min Place/Rank:
c85.png

- Filter to manually get the times for the two Bib#'s
- Transpose to get a record per Event
- Text to Columns to split out the Time form the Place provided
- Select to crean up the field names and data types
- Formula to make the manual adjustment of the one Time
- Filter to remove records with no Time
- Sort for new Place calc
- Multi-Row Formula to calc a basic Place
- Multi-Row Formula assign ties the min Place
- Summarize to bring the data to the Bib# level, averaging Place
- Sort for new Rank calc
- Record ID to make a basic Rank
- Multi-Row Formula assign ties the min Rank
- Filter to pull out Bib# 2421

I got rank of 11, and here is what i see when I look at the data:
- the Bib# originally with rank 11 was 4778, with an original average of 58
- Bib# 4778 has an Interval time of 3:51:34, and new time for Bib# 2421 is 3:31.48, so Bib# 4778 new average will be greater than 58
- for Bib# 2421, the original average of the three event ranks was 58
- an Interval time of 3:31.48 would be about 46th place for that event
Resulting in the new average for Bib# 2421 lower than 58, and the new average for the original rank 11 Bib# 4778 greater than 58, so Bib# 2421 will be rank 11.

What logic do you use to get rank 12?


As something separate, I found the full data at: https://cggames.com/scoreboard/ and noticed the data was in JSON, so I wrote another Alteryx workflow to pull and parse the data for all divisions, and then made a Tableau dashboard at: https://public.tableau.com/views/AlteryxChallenge85CGGames/Percentile

download.png
Percentile.png
There are filters and options to change the view, for example we can see @TaraM has solid times across the board, and especially so for the Endurance, in the top 28% of her Division:
Percentile.png


From looking at the data, there seems to be a data quality issue with 114 different people getting a time of exactly 360 seconds (6 minutes) for the Interval event:
Interval 360 Seconds.png
EstherB47
Fireball

My workflow shows Tara as placing 11th. Way to go Tara!!!

 

Spoiler
Instead of using multiple sorts and multiple RecordIDs or MultiRow formula tools, I left the data transformed, used one multi-row formula, and then used a summarize tool

Once Tara's interval score was updated, unioned that back into the original data set. The bib #s that don't join to the bib #s with missing times are the ones we want to actually rank.

Sorted on Name (event) and time (value) ascending for each, and then used a multi-row formula tool to assign ranking from 1-26.

Used a summarize tool to average all of the rankings per bib #, then sorted ascending on the average, and assigned a place with Record IDimage.png
Looking forward to everyone's creativity! I think there's a lot of space for different approaches here.
vishalgupta
Meteor

Lots of Regex, Sort and Record ID 

BenMoss
Alteryx Certified Partner

Enjoyed this one!

 

Spoiler
screen.PNG
Alteryx
Alteryx

The legend of @BenMoss folks!

 

Wins Inspire Europe Grand Prix.

Comes back for more challenges on his 1st day back from Inspire.