Rank transformation in Informatica is an active and connected transformation. The rank transformation is used to select the top or bottom rank of data.
Rank transformation is equal to RANK() window function in SQL. So rank transformation output ranks might not be consecutive numbers. For example, if two rows are ranked 1, the next rank is 3. The DENSE_RANK window function differs in that no gaps exist if two or more rows tie.
Read here: Difference between RANK() and DENSE_RANK
So to demonstrate this, we calculate both RANK() and DENSE_RANK() in same mapping for given input data.
Below is the sample mapping for rank transformation which is sourcing data from employee SQL Server table and loading the target data into a file:
In this example, we would like to rank the salary by department wise. So create rank transformation and drag the ports of source qualifier transformation into the rank transformation.
By default, the rank transformation creates a RANKINDEX port. The RANKINDEX port is used to store the ranking position of each row in the group. You can add additional ports to the rank transformation either by selecting and dragging ports from other transformations or by adding the ports manually in the ports tab.
In the ports tab, check the Rank (R) option for the port which you want to do ranking. You can check the Rank (R) option for only one port. Optionally you can create the groups for ranked rows. select the Group By option for the ports that define the groups.
Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as 10.
RANKINDEX will contain RANK() of salaries in each department.
Now we will calculate DENSE_RANK in next expression transformation:
Create below variables in expression transformation.
Dense_Rank:IIF(Previous_Dept != Dept,1,IIF(Previous_Sal = Salary,Dense_Rank,Dense_Rank+1))
Previous_Sal:Salary
Previous_Dept:Dept
Run the mapping with one workflow like below, for me ‘s_m_Rank_demo’ is the session and ‘wf_m_Rank_demo’ is the workflow.
After running, below is result of output:
Here we have both RANK() and DENSE_RANK() in single output.
