Challenge #85: How Did Tara Place in Competition?

[JoeM]

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.

[patrick_digan]

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]

@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]

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]

I am getting 14th rank.

[Joe_Mako]

Spoiler

In the attached, it handles ties by using the min Place/Rank:


- 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



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:



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:

[estherb47]

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 ID
Looking forward to everyone's creativity! I think there's a lot of space for different approaches here.

[vishalgupta]

Lots of Regex, Sort and Record ID 

[BenMoss]

Enjoyed this one!

Spoiler

[JoeM]

The legend of @BenMoss folks!

Wins Inspire Europe Grand Prix.

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