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.

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.

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!