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 #85: How Did Tara Place in Competition?

JoeM
Alteryx Alumni (Retired)

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

patrick_digan
17 - Castor
17 - Castor

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.

 

JoeM
Alteryx Alumni (Retired)

@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
11 - Bolide

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
11 - Bolide

I am getting 14th rank.

 

 

Joe_Mako
12 - 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
15 - Aurora
15 - Aurora

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
7 - Meteor

Lots of Regex, Sort and Record ID 

BenMoss
ACE Emeritus
ACE Emeritus

Enjoyed this one!

 

Spoiler
screen.PNG
JoeM
Alteryx Alumni (Retired)

The legend of @BenMoss folks!

 

Wins Inspire Europe Grand Prix.

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