Supports creating a new column with the rank for each subset of records relating to the same key. Ranking allows you to rank a table based on a specific column. For each subset of records associated with the same key in a table, a sequence number is assigned according to the relative value of a given column. Ranking is used, for example, when trying to identify the first products purchased by each customer, or the most recent product purchased.
Ranking creates a column with an integer value, which ranks records in the same table. The instruction syntax is:
Syntax
RANKING [Key to rank] [Column to use] [Column name] [Sorting order] ([Force sequence] [Filter])
Parameters
Key to rank
Defines a column that represents the key to be ranked. The column must be an index key in a table.
Column to use
Defines the column to use when creating the ranking numbers.
Column name
Defines the name of the new ranking column.
Sorting direction
Defines the sorting direction, ASC or DESC.
Force sequence
Defines the ranking sequence. In cases where two or more records have exactly the same value, checking Force sequence ensures that each record is allocated a different rank. If you do not check Force sequence, records with the same value have the same rank.
Filter
Defines a domain in case you want to filter the data.
How to create a ranking column
1 In Main file, choose New. The list of transformation instructions appears.
2 In the list of transformation instructions, choose Ranking. Ranking appears, as shown in Figure 5‑34.
Figure 5‑34 Creating a ranking column
3 In Key to rank, select a database, a table, and a column you use as a key for ranking.
4 In Field to use, select a database, a table, and a column you use to determine the ranking values.
5 In Name, type the name of the new ranking column.
6 In Direction, select the table‑sorting direction from the drop-down list.
7 Select Force sequence.
8 In Filter, choose Click to select define a domain, if you wish to filter the data.
9 In Description, type a short description of the column.
10 Choose OK. The Ranking instruction appears in Main file, as shown in Figure 5‑35.
Figure 5‑35 Creating a Ranking instruction
11 Save the project.
12 Run the instruction.
1 In Main file, select each instruction and then, choose - to make it inactive, as shown in Figure 5‑21.
2 In Data—Options, deselect Load, as shown in Figure 5‑36.
Figure 5‑36 Deselecting Load
3 Choose Run. The project runs and a confirmation message appears.
