Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Rank a list with groupings

_dateter
6 - Meteoroid
I want to rank a long list of records that contains groups and sub-groupings. The ranking would be based on each sub-group (e.g. 'Top 3 Products Purchased per Customer'). I think this should be fairly straight-forward but can't figure out how to do it. The data has three variables: Customer ID, Product Name, Number of Products Purchased. So for each unique customer id I need to sort and rank their top 3 products based on number of purchases. 

Input:
Customer ID / Product / Num
12345 / Widget 1 / 999
12345 / Widget 2 / 750
12345 / Widget 3 / 500
12346 / Widget 1 / 99
12346 / Widget 4 / 25
12347 / Widget 4 / 56
12347 / Widget 5 / 25

Desired Output:
Customer ID / Product / Num / Rank
12345 / Widget 1 / 999 / 1
12345 / Widget 2 / 750 / 2
12345 / Widget 3 / 500 / 3
12346 / Widget 1 / 99   / 1
12346 / Widget 4 / 25   / 2
12347 / Widget 4 / 56   / 1
12347 / Widget 5 / 25   / 2
15 REPLIES 15
tom_montpool
12 - Quasar

Either of the methods described will be affected by the sort order of the data going into the process. A sort tool prior to the RunningTotal or MultiRow-Formula will change the results that you get. A filter afterward will let you choose the records that you want to keep.

gloriaavalos
5 - Atom
 
troyfurnace
7 - Meteor

Another method I found is to use the Tile tool as described nicely in this article (https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Tile/tac-p/55615/highl...).  It is cleaner imo, and more closely fits how you'd define a rank, if you are familiar with those in RDBMSs.

CallieYuan
8 - Asteroid
 
saveeshkumar
5 - Atom

Simple way to implement the dense rank function.
Group the key column and assign the RecordID to each output and 
join with original values[Using Key Column].

saveeshkumar_0-1600417540478.png

 

MichelleMitchellLutz
6 - Meteoroid

This was very helpful!! Thank you so much for taking the time to explain. 

Labels