There are several ways to rank the records. The simplest way is to sort the records in ascending/descending order, ranking the records from top to bottom. The next way is how to rank is the same way but when the value is the same, that rank would be tie. The last way is when there is the tie rank, skip the ranks. The sample is as follows.

This post is about how to do that.
Input
This is the input for this post.

Let’s rank this data. What do you think to do? In this case, which tools can you use? Basically speaking, just sort the data, rank the records. But in some cases, it’s not so simple.
3 Ranking ways and grouped ranking
The simple ranking
This is the simplest way. Just rank the records from top to bottom order.

To rank by order, you have to sort records by the Sort tool descending, and then you can just use the Record ID tool.

Rank by group
Next is about to rank by group. In this case, I would like to rank by the State column, but the Record ID tool can not be used for grouped ranking.
For this purpose, use the Multi-Row Formula tool.

First, as above, sort the records descending by the Sort tool. The important point is sorting by State and Score column.

複数行フォーミュラのオプションは以下のとおりです。

Specify the State on the “Group By” option to rank by state. The “Expression” is just add one to the previous record as follows.
[Row-1:Rank]+1
The ranking allowing tie
I would explain this ranking system as below.

This way is when the score is the same, they are ranked as the same rank. In this case, you can not the Record ID tool, so you have to use the Multi-Row Formula tool or the Tile tool instead. First, I would like to explain how to use the Multi-Row Formula tools for this ranking.
Use the Multi-Row Formula tool

You have used the Multi-Row Formula tool for the grouped ranking, in this case the expression is more complex than that way.

This Expression measn that when the Score is the same as the previous row, the previous Score will be applied, otherwise the rank is added one.
IF [Row-1:Score]=[Score] THEN [Row-1:Rank]
ELSE [Row-1:Rank]+1 ENDIF
Use the Tile tool
Actuall, using the Tile tool is very easy for this ranking type.

The expression is not needed, just set correctly. Of course, remember to sort descending before using the Tile tool.

The “Tile Method” should be set to “Unique Value” and the “Unique Column” should be set to “Score” and check the “Leave Unsorted” option because when that option unchecked, the Designer automatically sort ascending by the “Unique Column”, the result is not that you wanted.
As a result, the rank is output as the “Tile_Num” column. “Tile_SequenceNum” is not needed, so you need to delete it by the Select tool.

Next, let’s rank allowing tie by group.
Use the Multi-Row Formula tool for ranking allowing tie
For the grouped ranking, you can use the grouped option on the Multi-Row Formula tool.

You just check the “State” column on the “Group By” option in this case.

Use the Tile tool for ranking allowing tie
You just check the “Score” column on the “Group by Columns” option in the Tile tool.

The ranking allowing tie with skipping the previous records number
Below I would explain a method that allows the same ranking but skips the number when the ranking is the same. For example, when there are two top persons, next rank would be 3rd as follows.

Actually, this way is a bit complex.
Use the Tile tool
First, I would like to explain the way to use the Tile tool. The whole workflow is as follows, which is to sort decending by Score and then use the Record ID tool. After that, use the Tile tool and culculate the Row number minus Tile_SequenceNum plus one.

This means that
This is done by subtracting Tile_SequenceNum from numbers assigned to row IDs that have the same rank (Tile_SequenceNum further assigns row IDs within the same value), and as the numbers are subtracted from the same rank, the same rank will be maintained until a different rank is reached.

The setting is as follows. The Tile tool is set to “Unique Value” as the “Tile Method” and to check the “Score” column on the “Unique Column” and also check the “Leave Unsorted” option.

The important point is expression on the Formula tool.

[Rank]-[Tile_SequenceNum]+1
This is very simple expression, but it works very well because this is a good way to use number patterns.
Use the Multi-Row Formula tool
You can also do it to use the Multi-Row Formula tool. In this way, the expression is also a bit complex. You can take the same approach as using the Tile tool, but in this post I would like to go another approach.
When the score is diffrent from the previous row, the rank should be changed. So at such records the rank is from the Row ID, otherwise the rank is from previous rows. It is also simple way.

Let’s see the Multi-Row Formula setting.

This expression is when the value is the same as the previous row, the TieRanking is from previous row’s TieRanking, otherwise from Rank.
IF [Row-1:Score]=[Score] THEN [Row-1:TieRanking]
ELSE [Rank] ENDIF
The important point is to rank by simple rank which is as record ID in advance. The result is finally from that rank.
Rank by group
Grouping ranking is to group by the “State” column. But you can’t use the Record ID tool for the grouping ranking, so you have to use the Multi-Row Formula instead.


You can see the detail on the sample workflow.
Conclusion
There are some ways to rank and I have explained how to rank by Alteryx. To be the honest, it is tedious to rank, so making the ranking macro might be the correct way.
Sample workflow download
The next blog post is …
The next article will be about the control of workflow executioin.
コメント