Scenario 6: How to Load only First and Last record of Source to Target

Scenario 6: How to Load only First and Last record of Source to Target.
(It's a very popular scenario in Interview. There are several approach for this but here I am opting simple and easy approach.)

Theory/Analysis
The Primary Challenge for this scenario is to finding total number of records in source and based upon that we need to filter records. Suppose total 16 records are there so, when record_id=1 and  record_id=16 then only load to target.

Practically

Step 1: Drop your Source twice. Suppose S1 and S2.

Step 2:
Aggregator
Connect Second Source Instance to Aggregator
Create one Output Port and Don't select any port as a Group.
Record_Count (Output Port) = COUNT(EMPNO)

Step 3: 
Joiner
Connect both S1, S2 pipeline to Joiner.
Join Type: Master Outer Join
Condition Tab: EMPNO=EMPNO1

Step 4:
Expression
Create Three Ports
Rec_ID (Variable Port)= Old_Rec_ID + 1
Old_Rec_ID (Variable Port) = Rec_ID
Out_Rec_ID (Output Port) = Rec_ID

Step 5: 
Router
Create two Group
First_Record Out_Rec_ID=1
Last_Record Out_Rec_ID=Record_Count

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.

hpandey.harsh@gmail.com