Scenario 13 B: How to Convert Row to Column

Scenario 13 B: How to four column data to eight column data.

(Source)
COL1,COL2,COL3,COL4
7369, SMITH, CLERK, 7902
17-DEC-1980, 800, 400, 20
7499, ALLEN, SALESMAN, 7698
20-FEB-1981, 1600, 300, 30
7521, WARD, SALESMAN, 7698
22-FEB-1981, 1250, 500, 30
7566, JONES, MANAGER, 7839
2-APR-1981, 2975, 400, 20
7654, MARTIN, SALESMAN, 7698
28-SEP-1981, 1250, 1400, 30

(Target)
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-Dec-80,800,400,20
7499,ALLEN,SALESMAN,7698,20-Feb-81,1600,300,30
7521,WARD,SALESMAN,7698,22-Feb-81,1250,500,30
7566,JONES,MANAGER,7839,02-Apr-81,2975,400,20
7654,MARTIN,SALESMAN,7698,28-Sep-81,1250,1400,30

Theory/Analysis
After analyzing source and target we can ascertain that we are getting HIREDATE till DEPTNO column value in second row so, we have to send second row value to HIREDATE, SAL, COMM and DEPTNO column.

Practically
Step 1:
Drag and drop one source and two target instance.
Note: Make sure in your target EMPNO is Primary Key. If it is not at database level then create at Informatica level.

Step 2
Sequence Generator
Create sequence generator and set properties below...
Start Value=1
End Value=2
Cycle (Check box should be checked)

Step 3
Expression
Create three ports
EMPNO (Variable Port) = IIF(NOT IS_NUMBER(COL1), EMPNO_OLD, COL1)
EMPNO_OLD (Variable Port) = EMPNO
OUT_EMPNO (Output Port) = EMPNO 

Step 4
Router
Create two Groups
First_Row  NEXTVAL=1
Second_Row NEXTVAL=2

Step 5
Connect first target instance from First_Row group (EMPNO,ENAME,JOB,MGR)

Step 6
Expression
Create five ports and connect expression from second group Second_Row of router.
EMPNO_UPD (Output Port)= TO_INTEGER(OUT_EMPNO1)

HIREDATE  (Output Port)= IIF(IS_DATE(COL13, 'DD-MON-YYYY')=1,TO_DATE(COL13,'DD-MON-YYYY'))

SAL  (Output Port)=TO_INTEGER(COL23)
COMM  (Output Port)=TO_INTEGER(COL33)
DEPTNO (Output Port)=TO_INTEGER(COL43)

Step 7
Update Strategy
Connect ports from Step 6 expression to Update Strategy
Update Strategy Expression= DD_UPDATE

Step 8
Connect ports from Update Strategy to second target instance.

BINGO!!!

Note: There is another method also... Could you think about that approach? Yes/No/May be... It's all yours. :-) 

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