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 #9: Analytics Ranking

GeneR
Alteryx Alumni (Retired)

Here is the new weekly challenge. The link to the solution for last challenge is HERE. For this challenge let’s look at ranking records when multiple records can have the same rank.

The objective is to determine the top 5 ranking based on the count, however since there are multiple rows with same count (similar to a round of golf) multiple people can be in the same place (Rank) if they have the same score.

 

We have listed this as a beginner challenge and I expect it will go very quickly for many of you. Let us know what you think, we are looking forward to hearing your feedback.

 

UPDATE 1/25/2016

The solution has been uploaded.

TaraM
Alteryx Alumni (Retired)

The solution has been uploaded.

Tara McCoy
alex
11 - Bolide

Slightly different solution  .2 sec vs .4 sec on my laptop Smiley HappyWeek 9 Solution.PNG

Rachel_Burge
5 - Atom

I was able to get to the solution using only these three tools! I was a little surprised this worked since I did not specifically tell Alteryx to put a 1 in row one of the Rank column. It must have assumed that if row: -1 is the header, it would assign row 1 a value of 0? Maybe oversimplifying this but it is pretty cool to see all the different ways alteryx can handle this task!

Solution to Excercise #9.png

mceleavey
17 - Castor
17 - Castor

A nice introduction to the "Tile" tool:

 

Spoiler
I first sorted the data by count in descending order. I then used the "Tile" tool to assign a new number for every unique value, with "Count" selected (ensure "Leave Unsorted" is checked).
I then simply applied a filter to the "Tile_Num" field to show top 5, then renamed the field.
Simple.

Solution.PNG

Results.PNG



Bulien

SeanAdams
17 - Castor
17 - Castor

came up with the same solution as @Rachel_Burge  - but glad to have read @mceleavey solution because that's a component I've not used before

MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @JoeM,

 

Ranks 1-5 as requested.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
KatieA
Alteryx Alumni (Retired)

Solution attached.

NicoleJohnson
ACE Emeritus
ACE Emeritus

Solution! I tried to think outside the box on this one, there are certainly several simpler solutions already posted. But why keep things simple when you can overcomplicate things with a transpose and a cross-tab? :)

 

Note: I really really really wish there was a "Max/Min" tool. (Yes, I will be suggesting this.) Seems like more than a few of us are regularly accomplishing this by using 2-3 tools - Sort + Sample, or Sort + Select Record, or finding the max of a field using Summarize + Join back all the other fields you need + Filter to weed out the non-matches... It seems like there should be a tool that lets you pick the max or min of something based on a certain field without having to use 2-3 tools to get there. Even better if there was one that let you pick the Top N or the Bottom N (which Sample does), but WITHOUT having to sort/etc. first. If i am completely missing out on a single tool that already does this, please oh please do share! I feel like I'm going crazy with these 2-3 tool "find the max" workflows! :)

 

Solution: Transpose (Group by Count, Columns = First, Values = Count), Sort by descending Count, Select first 5, Add Ranking via RecordID, Cross-Tab to re-orient the results, Filter out NULL values, rename/reorder columns.

WeeklyChallenge9.JPG

Laurap1228
11 - Bolide

My solution to #9, very close to the solution posted.

 

Spoiler
challenge9.jpg