Supports creating a new column with the rank for each subset of records relating to the same key. Ranking enables 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 direction] ([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, selecting 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 the Transform tab, choose “New”. The list of transformation instructions appears.
2 In the list of transformation instructions, choose RANKING. The Ranking screen appears, as shown in Figure 5‑30.
Figure 5‑30 Creating a ranking column
3 In the Key to rank fields, select a database, a table, and a column you use as a key for ranking.
4 In the Field to use field, select a database, a table, and a column you use to determine the ranking values.
5 In the Name field, type the name of the new ranking column.
6 In the Direction field, select the table‑sorting direction from the drop-down list.
7 Activate the Force sequence box.
8 Click on the Filter field to define a domain, if you want to filter the data.
9 Type a short description of the column in the Description field.
10 Click “OK” to confirm. The Ranking instruction appears in Main file panel
11 Save the project.
12 Run the instruction without loading data.
1 In Main file, select each instruction. Then choose the (-) icon to make it inactive.
2 Remove the selection from the Load box in the Data tab, as shown in Figure 5‑31.
Figure 5‑31 Removing the Load option
3 Choose Run. The project runs and a confirmation message appears.
