Rank Transformation Ports
The Rank transformation includes input, input/output, or output ports that are connected to another transformation in the mapping. The transformation also includes pass-through, and variable ports.
A Rank transformation has the following port types:
- Input
- Receives data from upstream transformations. You can designate input ports as input/output ports. The transformation must have at least one input port.
- Dynamic Port
- A port that can receive multiple columns to create a dynamic number of generated ports. A generated port is a port within a dynamic port that represents a single column. You can create input, output, and variable dynamic ports.
- Output
- Passes data to downstream transformations. You can designate output ports as input/output ports. The transformation must have at least one output port.
- Pass-Through
- Passes data unchanged.
- Variable
- Used for local variables. You can use a variable port to store values or calculations to use in an expression. Variable ports cannot be input or output ports. They pass data within the transformation.
Rank Index
The Developer tool creates a RANKINDEX port for each Rank transformation. The Data Integration Service uses the Rank Index port to store the ranking position for each row in a group.
For example, you might create a Rank transformation to identify the 50 highest paid employees in the company. You identify the SALARY column as the input/output port used to measure the ranks, and configure the transformation to filter out all rows except the top 50.
After the Rank transformation identifies all rows that belong to a top or bottom rank, it then assigns rank index values. In the case of the top 50 employees, measured by salary, the highest paid employee receives a rank index of 1. The next highest-paid employee receives a rank index of 2, and so on. When measuring a bottom rank, such as the 10 lowest priced products in the inventory, the Rank transformation assigns a rank index from lowest to highest. Therefore, the least expensive item would receive a rank index of 1.
If two rank values match, they receive the same value in the rank index and the transformation skips the next value. For example, if you want to see the top five retail stores in the country and two stores have the same sales, the return data might look similar to the following:
RANKINDEX | SALES | STORE |
|---|
1 | 10000 | Orange |
1 | 10000 | Brea |
3 | 90000 | Los Angeles |
4 | 80000 | Ventura |
The RANKINDEX is an output port only. You can pass the rank index to another transformation in the mapping or directly to a target.