How to Compute A Rank In #Alteryx

Introduction

I commonly have to rank data sets for a variety of reasons. Today I had to create a rank in Alteryx, which is something I have not had to do before.

Whenever I do not know how to do something, I do a search on the topic. The search for “ranking in Alteryx” did not lead to an easy-to-follow example, so I decide to write this blog post. I am sure that there are multiple ways of ranking items in Alteryx, but this method is what I created today for my situation. This article just describes another one of my basic Alteryx data building-block techniques.

The Data Structure

Figure 1 shows an example of the data I wanted to rank. In one column, there is an identifier and in another column, there is a date that represents something that happened on that day. For each identifier, there is a variable number of records, ranging from 1 to nearly 100 events. The objective is to have Alteryx assign the third column, which I have named “MyRank”. The purpose of this post is to show how that is completed.

Figure 1 - The data in the first two columns was sorted and then the rank in  the third column gets created in Alteryx.

Figure 1 – The data in the first two columns was sorted and then the rank in the third column gets created in Alteryx.

 

Sort the Data Before Calculating the Rank

Prior to establishing the MyRank field shown in Figure 1, I sorted the data by identifier and then by date. This sorting is necessary for this procedure to work properly. The rank is simply a number starting at 1 and it gets incremented by 1 for each successive record that exists for each occurrence of the identifier.

The Alteryx-Calculated Ranking Function

Figure 2 shows the multi-row formulation that I used to create the MyRank values. The multi-row tool is exactly what is needed to create the rank.

Essentially, the multi-row tool allows you to create a new variable, which I call “MyRank”.  I assigned it as an Int32 variable type, although with only a maximum of 100 occurrences, I could have chosen an Int16.  Next, I chose the field called ID Number (which is the same field I renamed to Indentifier in Excel, as shown in Figure 1) as the Group By Option because it is the field that will control how the rank is calculated. The expression I developed to calculate the rank is a simple if-then-else block which works as follows.

Figure 2 - The multi-row tool is used to create the MyRank values.

Figure 2 – The multi-row tool is used to create the MyRank values.

 

If the ID Number of the current row is the same as the previous row ([Row-1: ID Number]) then take the MyRank value stored in the previous row ([Row-1:MyRank]) and add 1 to it.  Else, if the ID Number is not the same, it means a new ID Number has been encountered, so assign a value of 1 to the MyRank value.  That last part just initializes the MyRank value for the first occurrence of a new ID Number.

Final Thoughts

Sometimes having a little tutorial like this makes life easier. I know that I’ll forget how to do this and one day I will be revisiting these words. Lastly, I have used this same approach for more than 20 years in Excel to rank values. This technique is nothing special but learning how to do it in Alteryx is what makes it fun. I hope this post gets expanded in the future with additional clever methods for ranking data.

 


If you like this article and would like to see more of what I write, please subscribe to my blog by taking 5 seconds to enter your email address below. It is free and it motivates me to continue writing, so thanks!

Enter your email address to follow this blog and receive notifications of new posts by email.


2 thoughts on “How to Compute A Rank In #Alteryx

  1. Hi Ken,

    I think if you make the expression just [Row-1:MyRank] + 1 then it will work just as well. Selecting to group by the [ID Number] field will automatically deal with the reset for you.

    Of course what you have works fine, but just thought you would be interested in learning something new.

    Cheers
    Adam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s