Ranking values in multiple fields independently

[dgr2017]

Hello,

I have a table that looks something like this:

	a	b	c
1	4283	49	295
2	3390	506	432
3	712	246	825
4	175	49	100
5	7	0	393

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

	a	b	c	a_rank	b_rank	c_rank
1	4283	49	295	1	3	4
2	3390	506	432	2	1	2
3	712	246	825	3	2	1
4	175	49	100	4	3	5
5	7	0	393	5	4	3

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

[TonyM]

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]

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 · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

Wanted to pass along the solution to the broader community.

Thanks to @scottj for sending this one over!

[dgr2017]

Awesome, thanks for passing along Tony! 

[cliffjordan]

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."

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

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

Enjoy!

-cliff