How to Create a Rank in Alteryx

LinkedIn

This is the third time in about 6 weeks that I’ve needed to create a rank in Alteryx and couldn’t remember how. Each time I had to dig through projects to find what I did last time. That means it’s time to write it up so I can find it next time. Read more to learn an easy way to create a rank in Alteryx.

Now, Alteryx doesn’t have a simple rank function like Spotfire. In Spotfire, I can write something like this….

Rank([Sales],[Region])
Rank([Oil Production], [Region], [Well])
Rank([Phase Start Date], [Well])

The first expression above would rank sales for each Region. The second expression would rank Oil Production for each Well in each Region. The third expression will rank the Phase Start Dates for each Well. What’s great about this function is that it will take the data type into account in the ranking. It has no problem ranking dates or numbers. In Alteryx, I have to use more than one tool rather than one simple expression.

Use Case

My use case this week was pretty simple. I wanted to know the order that a well’s phases happened in. My final output looks like this.

I get the output above with the following collection of tools.

Workflow

Tools

Here’s what each tool is doing.

  1. Filter — The data set includes records where the phase has been entered, but it has no start and end date. This has the potential to mess up my rank, so the Filter tool gets rid of them.
  2. Formula — The Formula tool creates a column called “phase counter” which puts a value of 1 in each record. This will be used by the Running Total tool.
  3. Sort — My Sort tool sorts by the well and the start date so that the Running Total tool will calculate correctly.
  4. Running Total — The Running Total tool to creates my rank by adding up the phase counter column for each well.
  5. Select — I use the Select tool to rename the column the Running Total tool creates to “phase order”.
  6. Union — Finally, I union the phases without start and end dates and the rest of the data set to put my data set together again.

Now, it wasn’t required, but I also have a Formula tool in the workflow with the annotation — phase order = 0. This tool creates a column called called “phase order” where the value is 0 for my records where the phase has no start or end date.

Wrap Up

Now you know how to create a rank in Alteryx! In conclusion, I’ve tried other methods to create a rank, including using the Multi-row Formula tool. However, I keep going back to this method because it works no matter what data type you are working with. If you have another method, please leave a comment. I’d love to learn how others approach the problem.

Alteryx Version

Content created with version 2019.4.8.22007

Other Alteryx Posts

Post Views: 4,815
Tags: