Challenge #436: Universal Summer Cup Stats – Part 2

[SylviaP]

Hi Maveryx, a little earlier than usual!

A solution to last week’s challenge can be found here.

We are thrilled to present the second and final challenge in the spirit of the Maveryx Summer Cup 2024 that runs from July 24 through August 11, 2024. During the event, you can complete many different activities in Community, including this challenge and any other published Weekly Challenge or Cloud Quest to earn points and win a medal that will appear as a badge on your profile,.

We would like to take a moment to acknowledge ACE Calvin Tang (@caltang)  for his outstanding contributions to our Community. Thank you, Calvin, for being an active member and providing us with this fantastic idea as part of our campaign to feature user-submitted challenges for special occasions!

In Part 1, you discovered that there are athletes competing in sports from various constellations in a universe-wide event! Every summer these extraterrestrial athletes meet and compete across the galaxy in the Universal Summer Cup.

The dataset provided contains statistics from 40 Universal Summer Cups. It is vertically sorted, which is not a common practice for data analysis. However, this is a familiar scenario for our users, who often deal with messy and unstructured data.

Your task is to identify the Summer Cup where each constellation (team) earned its highest total medal count (gold, silver, and bronze). The performance metric is the total number of medals won, and the final output should be a unique list of constellations, the Summer Cup event when the constellation performed its best, and their total medal tally (if there is a tie in the number of medals, consider the most recent Summer Cup).

Need a refresher? Review the following lessons in Academy to learn more and earn points during the event:

• Multi-Field Formulas
• Separating Data into Columns and Rows

Good luck!

[Yoshiro_Fujimori]

It was a fun challenge. Thank you @caltang!

Spoiler

Workflow

Formula Tool
Medal = Switch(
mod([RecordID], 5),Null(),
2, "Gold",
3, "Silver",
4, "Blonze",
0, "Total")

Multi-Row Formula Tool
Team = IF IsInteger([Value]) OR [Value] = "-"
THEN [Row-1:Team]
ELSE [Value]
ENDIF

[AmandaTSY]

Challenge was tough and thinking there should be a more concise way than mine. Also, the target output seems like it takes the smaller summer cups as the most recent summer cup? I have assumed that the larger the summer cup, the more recent it is. 

[Krzysiek]

MySolution

I used a batch macro to add yet another layer of 'fun' to the task.

Spoiler

[kapilrautela_1990]

Spoiler

Spoiler

[Christina_H]

My solution

Spoiler

[Supriya2203]

Interesting Challenge!

Assumption: Larger the summer cup, the more recent it is. 

[griffinwelsh]

My solution

[AkimasaKajitani]

My soltution. 

Spoiler

 

Fun challenge! @caltang 

[Kenda]

Spoiler