Wednesday, 23 October 2013

Informatica Scenario Based Interview Questions (Last 3 rows)

Interviewer asked “How  will you get first 3 records from flat file source? “...You will say in your mind that’s simple

You reply “We can use variable in expression and increment it and the use filter transformation to pass just first two records”

Interviewer will come up with a new trick in his sleeve “How  will you get last 3 records from flat file source? “

Déjà vu …J

You will think and try to build some logic on the fly and try to explain may we can use this transformation or some another transformation and will get bit confused.

I have tried to explain this with a simple example

Suppose we have customer data coming in below format and we want to have last 3 rows in target


Create expression transformation (exp_Build_Logic)  drag all three input ports and create three extra ports as below

Ø  v_dummy (Output Port) –Assign Value 1 to it
Ø  v_Total_Count (Variable Port) := v_Total_Count+1
Ø  o_Total_Count := v_Total_Count

Step2) Create Aggregator transformation with all ports and do not choose any port as group by port

Step 3)

 Create Joiner transformation jnr_Agg_Expression as Sorted Input (You need to choose this option as you are trying to use two sources originating from same source)

Take two ports from aggregator and rest from expression transformation and join on field v_dummy

Output from joiner

Step 4)  Create filter transformation after joiner with following conditions

This will past last three records


Output will be



No comments:

Post a Comment