Last row in a set of records in informatica

Question

I need to identify the last row in a set of records and assign a flag to that row using informatica, not sure whether aggregator will do the job or rank. The grouping is based on multiple columns. Any suggestions please?

Answer 1

you can use rank or aggregator and then joiner transformation.

Aggregator - my preference.

• First of all order the data by keys. you need to be careful about the order since order will determine which row is flagged. order should be Asc. Assuming you have a key and then you have a date column on which you want to find latest. so order by keys first and then order by date column.
• Then use an aggregator - here use all key columns as group by. This will ensure the last column pass through the aggregator. Pls note, you need to mention only key columns in group by. Then use an expression to create a flag column.
• After that use a joiner to join this aggregated data back to main flow. Join condition will be on key values. Pls note, you need to mention only key columns in join condition. So, joiner output will be all data + flag_value.

Whole mapping should be like this -

SRT -->AGG--> EXP-->|
 |----------------->|JNR --> next transformations            

Rank This is similar to above process. only difference is we will use rank instead of aggregator. So, follow above steps with exceptions of rank. In rank transformation, pls mention, you want top row, use correct sort by column.

Mapping should be like this

SRT -->RNK--> EXP-->|
 |----------------->|JNR --> next transformations