How to get row_number() in informatica

Question

How to get the result that we get in oracle ROW_NUMBER() OVER (PARTITION BY ID) from flat file in informatica

Answer 1

You can get somewhat similar functionality using the Rank transformation.

In the rank transormation, select the GroupBy option for the ports you would use in PARTITION BY. Specify the rank port as the port on which you would do ORDER BY. You can select only 1 rank port, however. Select Top/Bottom in properties for Descending/Ascending order respectively

Also, you have to manually specify the maximum number of records you are expecting in each PARTITION in the Number of Ranks property in Rank transformation. You can put a number that will always be greater than the count of records in any partition/group.

The RANKINDEX output from Rank Transformation will be your row number.