Challenge #85: How Did Tara Place in Competition?

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.

challenge_85_start_file.yxmd

challenge_85_solution.yxmd

Parse

Join

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Transform

Accepted answers

All comments

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.

challenge_85_start_file.yxzp

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!

challenge_85_Ceneviva.yxmd

gnans19

I am getting 14th rank.

challenge_85_start_Gnans.yxmd

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:
Interval 360 Seconds.png

Download CG Games Scorecard.yxmd

challenge_85_Joe_Mako.yxmd

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.

challenge_85_EHB_solution.yxmd

vishalgupta

Lots of Regex, Sort and Record ID

challenge_85_Vishal.yxmd

BenMoss

Enjoyed this one!

Spoiler

challenge_85_start_file - BM.yxmd

JoeM

The legend of @BenMoss folks!

Wins Inspire Europe Grand Prix.

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

Natasha

Solution attached. Btw @JoeM it looks like you attached the start file twice instead of the solution.

Spoiler

challenge_85_NK.yxmd

JoeM

Thanks @Natasha! Updated!

NicoleJohnson

My solution!

Spoiler

My results gave Tara 11th place overall, and 54th in the Interval event. First, I transposed the data and then isolated Tara & Carisa to calculate the time that Tara should have had for the Interval event. I joined the data back together, and then sorted by event & used a multi-row tool to determine what the ranks should be with the addition of the Tara's new time for Interval.I then joined that information to a list of Bib#'s summarized by only those with 4 timed events. Finally, I calculated the average rank per Bib# to determine Tara's final ranking.

challenge_85_NicoleJohnson.yxmd

samjohnson

I don't trust authority so I eliminated the initial rankings of each event and re-calculated them

My solution is longer because I wanted to take a more step-by-step approach, and I didn't try to automatically calculate Tara's Interval time; I set it manually. (This hurt me to do this, but there's no harm as we're not curing cancer, right?)

Finally, after re-calculating all of the event rankings I took the overall average of those ranks as a Double and sorted them and added another record ID, which became the final place for each competitor. I finished by highlighting Tara's entire record.

Fun challenge.

challenge_85_start_file.yxmd

LordNeilLord

I initially got a rank of 13, so I checked and double checked the workflow but couldn't find the issue! After several moments of head scratching...I finally realised that the times were in mm:ss.ms and not hh:mm.ss....a few quick formula changes and I got the correct result.

Spoiler

challenge_85_LNL.yxmd

robcarroll

Done!

challenge_85_solution.yxmd

LandonG

Solution attached.

challenge_85_start_file.yxmd

philipmannering

Well done Tara. Solution attached.

Spoiler

11th

Spoiler

challenge_85 - How did Tara Place in Competition.yxmd

ggruccio

Very satisfying when it matched!

challenge_85_gg_finish.yxmd

samN

guess i thought the average was of the overall times, wondered why my answer was off, but fixed that

challenge_85.yxmd

jamielaird

Here's my solution, with some stars, just because..

Spoiler

PS - proper Millisecond support please!

challenge_85_solution_JL.yxmd

jasperlch

Solution attached.

challenge_85_JL.yxmd

JosephSerpis

Challenge Completed

Challenge_85_Joe_Serpis.yxmd

CHarrison

I found this one quite challenging!

Challenge 85.yxmd

tochy

challenge_85_start_file_Tochy.yxmd

dsmdavid

Belated "Well done", Tara :-)

Spoiler

Spoiler

I really struggled with this. It took me at least three redesigns to get the right answer.

Spoiler

challenge_85_RaceRanking.yxmd

StevenBetts

My solution is a bit 'extra' but I got there in the end.

challenge_85_start_file_SMB.yxmd

JonM

Enjoyed this challenge thank you! Did not realise that Alteryx's date formats do not support micro seconds, however as always Alteryx was able to adapt!