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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ranking values in multiple fields independently

dgr2017
7 - Meteor

Hello,

 

I have a table that looks something like this:

 

 abc

1

428349295
23390506432
3712246825
417549100
570393

 

In the end, I'm looking to rank values in each field to produce the following output:

 

 abca_rankb_rankc_rank

1

428349295134
23390506432212
3712246825321
417549100435
570393543

 

A couple notes:

The values in one field are ranked independently of values in other fields

Please notice how the ranking applies to duplicate values (49 in column b)

 

I understand how this approach (http://downloads.alteryx.com/Alteryx/Help/Common_Tasks/RankField.htm) could work if I were only ranking a single field but as you can see above, that's not the case. Ranking multiple fields individually is what I'm struggling with. I could do it iteratively (e.g. sorting and ranking each field individually) but hoping someone can recommend a more efficient approach. Any assistance is greatly appreciated?

 

Thanks,

 

Dave

 

8 REPLIES 8
TonyM
Alteryx Alumni (Retired)

Hi @dgr2017

 

Attached is a workflow that will help make this process dynamic rather than having to iterate through each field you would like to rank.

 

The basic idea is to transpose and sort the data so that you can use a multi-row formula to calculate the ranks. I then cross tab the data and the rankings back together so that the ranks are appended to the original data.

 

Let me know if this helps!

dgr2017
7 - Meteor

Thanks for such a quick response Tony! I'm just getting a chance to look at the workflow you provided and it's exactly what I'm looking for with the exception of one nuance. Looking for the largest value in each field to receive a rank of 1. So, in Field 1 of the workflow you provided, RecordID 4 would receive a rank of 1 and RecordID 1 would receive a rank of 4. I'm trying to figure out using the workflow you provided but any ideas are greatly appreciated. Thanks again for all the help!

 

Regards,

 

Dave

TonyM
Alteryx Alumni (Retired)

Hey @dgr2017

 

Glad to help!

 

Slight oversight on my part. I've adjusted the sort and multi-row tool to reflect what results you are expecting.

 

Best!

dgr2017
7 - Meteor

Exactly what I was looking for! The odd thing is Alteryx is producing rankings that differ from what I'm getting in SAS. I'm thinking it might have to do with some additional logic that SAS is using but that's on me to look into. Thanks again for all the help!!

 

Cheers,

 

Dave

dgr2017
7 - Meteor

Hi Tony,

 

Thanks again for providing me with the ranking solution! It does exactly what I'm looking for.

 

The challenge I'm having is I'm not getting the same results SAS is producing. I've attached a sample to illustrate what's going in.  The first two columns are associated with Alteryx and the second two SAS. The counts are identical and ranked in descending order. The difference is Alteryx ranks duplicate values incrementally while SAS doesn't.

 

For example, if you take a look at rows 4 and 5, the count of 19020 is duplicated and both receive a rank of 3. However, Alteryx gives the next count (17692) a ranking of 4 while SAS gives it a ranking of 5. It appears SAS skips ranks based on the number of duplicate counts (1 in this case). As a side note, there are cases where counts are repeated more than once. You can see this with the count of 9057 in the attachment.

 

If this was a simple ranking exercise, all would be good. Unfortunately, the next step of the process (SAS) I'm trying to replicate involves flagging records with a rank of <51 and so I'm getting different results. Alteryx is flagging more records than SAS as a result of what's laid out above. I've been trying to figure out a solution over the past couple of days but hit a wall and hoping you or someone might be able to suggest a solution. Thanks again!

 

Regards,

 

Dave

 

 

 

 

TonyM
Alteryx Alumni (Retired)

Wanted to pass along the solution to the broader community.

 

Thanks to @scottj for sending this one over!

dgr2017
7 - Meteor

Awesome, thanks for passing along Tony! 

cliffjordan
8 - Asteroid

Dave, 

I have often run into the same scenario, so I created a Rank Macro called RankMacro.yxmc that you can use. You use it like any other Preparation Tool and just choose which fields you want to create ranks for. 

 

Here are the details:

"RankMacro is a standard macro that will rank fields within a record. The user chooses the fields he wants to Rank and the direction of the Rank (Descending or Ascending). The macro creates an additional field for each of the chosen rank fields to store the Rank value. These new fields will have the same name with a "_Rank" suffix."

 

cliffjordan_0-1578276971783.png

 

You can download this from the Gallery. Here is the link: 

https://gallery.alteryx.com/#!app/RankMacro/5e12839c8a933710f08216c2

 

Enjoy!

-cliff

 

Labels