Rank By X
Use the Rank By X tool to assign a sequence number (to each subset of records associated with the same key in a table) according to the relative value of another field in the table (the X).
For example, in a transaction table containing the fields
TransID,
CustID and
TransDate, where a customer has made multiple purchases there will be a group of records for that customer. Ranking allows this subset of records to be ordered based on the sort of the TransDate field so the record with the earliest transaction date will have a rank of 0, the next one will be 1 and so on. The resulting column can be used to easily extract information like
First transaction or
Most recent transaction.
The example below demonstrates how to rank members of a household by their profitability.
This tool is often used in conjunction with the
Copy Values To tool to create columns at higher database based on the rank of fields in foreign tables. Using the example below, you could create a column at the
Household level which contains the
CustID of the most profitable customer in the household. See the 'Copy Values To' tool topic for a step by step example of how this can be achieved.
Procedure
- Drag the
Rank By X
tool to the Workspace.
- Choose the join operator.
- Click the icon to the right of the
New Rank By X field.
- In the
Display Name field, enter a name for the column.
If you save the tool as a template, this name will be used to display it in the
Templates tab. The Display Name is also used in the Caption area if you insert the tool into a document.
- If you want the new column to be automatically indexed, select the
Index Column check box. You should only index columns when necessary. For example, when engineered columns have > 250 discrete values, indexing can start to improve query performance.
| Note: |
The default value for the
Index Column check box is unselected. If you import a Campaign Manager document with the
Index Column check box selected, the check box will revert to unselected. This is because when you import an existing engineering node, you are effectively creating a new one and the check box default of unselected is applied. |
- Optional setting: if you want to make the column permanent, click the
Optional expand icon. In the
Table Column Name field, enter the name you want to use to display the column in the
Data Explorer tab.
- Configure the remaining
Settings fields:
- Column Values to Rank: Select the column which will be used as the basis of the rank. This would usually be a date like “Transaction Date” or a numeric value - “Customer Profit” in our example.
- Group by Column: This is the column that links the ranked column values and which is used to group them together for ranking. It would usually be a key column like Customer ID or Household ID. The selected Group by Column’ must exist in the same table as the ranked column, so in our example we select Household ID from the Customer table not from the Household table. All processing for this tool takes place in a single table.
- Group by Sort Order: Selecting Descending will allocate the highest value (or latest date) with a rank of 0’. Selecting Ascending will allocate the lowest value with a rank of 0’.
- Filter Segment: Add a standard report filter to reduce the scope of the results.
- Duplicate Values: Where there are duplicate values for the ranked column, for example “multiple transactions on the same date” or “multiple people in a household with same profitability” then this field can be used to specify how to allocate the rank.
- Force unique rank
randomly allocates rank numbers where there are identical values.
- Allow duplicate rank values assigns identical values with the same rank number.
- Copy Values To:: the Rank by X tool is often used in conjunction with the Copy Up tool to create columns at higher database based on the rank of fields in foreign tables. This option allows the user to create this link directly. Click on the drop down to select the
Copy Values To tool or the
First or Last
tool which will select the first or last value from your ranked column.
- Click
Process Document to create the ranked column.
As an example, the following screen is configured to create a ranked column of customers according to Customer Profit and grouped by Household.
- Embed one of the following tools to take the results of a Rank by X to engineer a new column on another table:
- Copy Values To: used to create, for example, a column of most profitable customers’ on the household table.
- First or Last: simplifies the extraction of the first, last, or a specific value from a transaction table, for example the first transaction date or sales channel of first purchase.
|
|
