Rank Tool
Use Rank to rank your data for further processing or output. Select one or more ranking types, sort by columns, specify order, and optionally group by columns.
AMP-Only Tool
The Rank tool is only available via the AMP engine.
Configure the Tool
Use these options to configure the tool:
Ranking Type
Choose one or more ranking types. Your options are...
Ordinal Ranking: (Default) All items receive distinct ordinal numbers, even if some items are equal.
Dense Ranking: Equal items receive the same ranking number, and the next items receive the following number.
Standard Ranking: Each item is ranked by its position in the sorted list. Equal items share the lowest possible rank. Subsequent items are ranked as if the equal items occupied consecutive positions.
Modified Competition Ranking: Items are ranked by their position in the sorted list. Equal items share the same rank, corresponding to their position. The next item receives the following rank, regardless of the number of ties.
Fractional Ranking: Equal items receive the same ranking number, which is derived from their position in ordinal rankings. Specifically, it equals 1 plus the count of items ranked above them, plus half the count of equally ranked items. This method maintains the sum of ranking numbers consistent with ordinal ranking.
Use Dictionary Order
This setting is not selected by default. Use the associated dropdown, then select a character or language set to sort by.
Sort Method
Depending on the language of your Designer instance, a different default sort order might be used. Consult the Language and Region User Settings to read more about the default Sort Method.
Select Columns
In the Select Columns section, use the dropdowns to select the columns you want to rank and the order (Ascending or Descending) to rank in. Use the up and down arrows to reorder the rank columns for output. Use the minus button to remove a rank column.
Group by Column (Optional)
Select columns from your dataset to group by. Use the Select All check box if you want to group by all columns. If a group or groups are specified, values are ranked within each unique group.
Ranking Examples
Please review the table below for an example output depending on the type of ranking selected. The ranking is based on the data in the Units Sold
column with order set to Descending.
Record | Units Sold | Ranking Type | ||||
---|---|---|---|---|---|---|
Ordinal | Standard | Dense | Modified Competition | Fractional | ||
1 | 22 | 1 | 1 | 1 | 7 | 4 |
2 | 22 | 2 | 1 | 1 | 7 | 4 |
3 | 22 | 3 | 1 | 1 | 7 | 4 |
4 | 22 | 4 | 1 | 1 | 7 | 4 |
5 | 22 | 5 | 1 | 1 | 7 | 4 |
6 | 22 | 6 | 1 | 1 | 7 | 4 |
7 | 22 | 7 | 1 | 1 | 7 | 4 |
8 | 21 | 8 | 8 | 2 | 14 | 11 |
9 | 21 | 9 | 8 | 2 | 14 | 11 |
10 | 21 | 10 | 8 | 2 | 14 | 11 |
11 | 21 | 11 | 8 | 2 | 14 | 11 |
12 | 21 | 12 | 8 | 2 | 14 | 11 |
13 | 21 | 13 | 8 | 2 | 14 | 11 |
14 | 21 | 14 | 8 | 2 | 14 | 11 |
15 | 20 | 15 | 15 | 3 | 17 | 16 |
16 | 20 | 16 | 15 | 3 | 17 | 16 |
17 | 20 | 17 | 15 | 3 | 17 | 16 |
18 | 19 | 18 | 18 | 4 | 20 | 19 |
19 | 19 | 19 | 18 | 4 | 20 | 19 |
20 | 19 | 20 | 18 | 4 | 20 | 19 |