Rank transformation also provides the feature to do ranking based on groups. Like if you want to get top ten salaried employee department wise, then this grouping can be done with this transformation. Rank transformation is an active transformation, as it affects the number of output rows. The rank transformation has an output port by which it assigns a rank to the rows. Our requirement is to load top 3 salaried employees for each department; we will implement this using rank transformation. Step 1 – Create a mapping having source EMP and target EMP_TARGET
Step 2– Then in the mapping
Select transformation menu Select create option
Step 3 – In the create transformation window
Select rank transformation Enter transformation name “rnk_salary” Select Create button
Step 4 – The rank transformation will be created in the mapping, select done button in the window
Step 5 – Connect all the ports from source qualifier to the rank transformation
Step 6– Double click on the rank transformation and it will open “edit transformation window”. In this window
Select properties menu Select “Top” option from the Top/Bottom property Enter 3 in the number of ranks
Step 7 – In the “edit transformation” window again
Select ports tab Select group by option for the Department number column Select Rank in the Salary Column Select ok button
Step 8 –Connect the ports from rank transformation to the target table
Now, save the mapping and execute it after creating session and workflow. The source qualifier will fetch all the records, but rank transformation will pass only records having three high salaries for each department.
