Scenario 5C: How to Convert Column To Row (4)

Scenario 5C: How to achieve target below...

(Source)

ID,NAME,LOC
101,null,null
null,SIMITH,null
null,null,USA
102,null,null
null,JOHN,null
null,null,UK

Note: NULL are just for representation. There is no value in that column.

(Target)
ID    NAME    LOC
101 SIMITH USA
102 JOHN UK

Let's Chat: This scenario is just opposite of Scenario 13 A. There we had first converted row to column then column to row. Here we are going to do just opposite.
In case you want to bush up Scenario 13 A.

http://informaticachamp.blogspot.in/2014/03/scenario-13-how-to-convert-row-to-column_16.html

Theory/Analysis:
By analyzing source and target we can conclude that first we need to convert column to row by using Normalizer then we will get three occurrence of each value filter it out based upon if value is not null. Then it's simple concat all value in set of three means concat from ID till LOC value. Ok... Now, we have all the data in rows but in single column (here once again we to convert column to row) so, use expression split into three column value that's it.  gotchaaa or confused? Let's do it practically

Practically

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

Step 2:
Normalizer
Create one port in Normalizer tab

Column Name  Occurs    Data Type   Prec
ROWS             3               String          100

Step 3
Filter
Connect ROWS and GCID_ROWS from Normalizer to Filter

Filter Condition= NOT ISNULL(ROWS)

Step 4
Expression
Create three ports

ADD_ROWS (Variable Port) = IIF(ISNULL(OLD_ROWS) OR GCID_ROWS=1, ROWS, OLD_ROWS||','||ROWS)

OLD_ROWS (Variable Port) = ADD_ROWS

O_ADD_ROWS (Output Port) = ADD_ROWS

Step 5
Filter
Connect GCID_ROWS and O_ADD_ROWS column from Expression

Filter Condition=GCID_ROWS=3

Step 6
Expression
Create three ports

ID (Output Port)= SUBSTR(O_ADD_ROWS, 1, INSTR(O_ADD_ROWS, ',',1,1)-1)

NAME (Output Port)= SUBSTR(O_ADD_ROWS, (INSTR(O_ADD_ROWS, ',',1, 1)+1), ((INSTR(O_ADD_ROWS, ',',1, 2)- INSTR(O_ADD_ROWS, ',',1, 1)-1)))

LOCATION (Output Port)= SUBSTR(O_ADD_ROWS, (INSTR(O_ADD_ROWS, ',',1, 2)+1))

Step 7
Connect ID, NAME, LOCATION to your target.

Now, what are you waiting for? Run it and get your expected output. :-)

BINGO!!!

In Case If you are interested to see others Converting Column to Row Scenario...

http://informaticachamp.blogspot.in/2014/03/scenario-5-how-to-convert-column-to-row.html

http://informaticachamp.blogspot.in/2014/03/scenario-5a-how-to-convert-column-to-row.html

http://informaticachamp.blogspot.in/2014/03/scenario-5b-how-to-convert-column-to.html

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