Scenario 5B: How to Convert Column To Row (3)

Scenario 5B: How to Achieve Target below...

(Source)
ENAME,ENAME1,ENAME2
John,Mathew,Stev
Parker,Heden,Waugh

(Target)
FNAME,LNAME
John,Parker
Mathew,Heden
Stev,Waugh

Theory/Analysis
After analyzing source and target we can conclude that LNAME is coming into second row. After converting column to row we have to combine first row and second row in such a way so, that it's load as FNAME and LNAME to Target.

Practically

Step 1: 
Drag and Drop your source and target to mapping designer.

Step 2:
Normalizer
Create a port in Normalizer Tab

PORT_NAME       DATA_TYPE      OCCURS
NAME                   String                     3

Step 3
Sorter
Take Name and GCID_NAME column to Sorter from Normalizer.
GCID_NAME (key)  Check

Step 4
Expression
Create Six Ports
GCID_COMPARE (Variable Port)= IIF(GCID_NAME!=OLD_GCID, GCID_NAME,OLD_GCID)

FNAME_LNAME_FLAG (Variable Port)= IIF(GCID_COMPARE=OLD_GCID, 1,0)

OLD_GCID  (Variable Port)= GCID_COMPARE

FNAME_LNAME_CONCAT (Variable Port)= IIF(NOT ISNULL(FNAME_LNAME) AND FNAME_LNAME_FLAG=1, FNAME_LNAME||','||NAME, NAME)

FNAME_LNAME (Variable Port)=FNAME_LNAME_CONCAT

OUT_FNAME_LNAME (Output Port)=FNAME_LNAME

Step 5
Aggregator
Drag and drop OUT_FNAME_LNAME and GCID_NAME column to Aggregator
Select GCID_NAME as Group Port (Group Check)
Create two Output Port

FNAME (Output Port)= SUBSTR(OUT_FNAME_LNAME, 1, INSTR(OUT_FNAME_LNAME, ',',1,1) -1)

LNAME(Output Port)= SUBSTR(OUT_FNAME_LNAME, INSTR(OUT_FNAME_LNAME, ',',1,1) +1)

Step 6: Connect FNAME, LNAME to Target.

BINGO!!!

LIMITATION: As I had already said solution of this kind of scenarios are always close ended means, if your source has been changed then solution above will not work. Solution above will work only if two records are there in Source. If you have more than two rows in your source then logic to Split FNAME and LNAME will be also changed in Aggregator.

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