Informatica column concatenation

Question

I have 2 columns in a single my input as given below :

Input :

Column1         

A|B|C|D 

Column2

1|2|3|4

And I need to get the single Output as shown below :

Column1

A.1 || B.2 || C.3 || D.4

Can anyone help me to get the output ?

Answer 1

You can split the columns into multiple parts based on delimitter and then concat.

• 1 find position of | in first column like this -

v_pos1 = InStr( col1, '|', 1, 1)
v_pos2 = InStr( col1, '|', 1, 2)
v_pos3 = InStr( col1, '|', 1, 3)
...

• 2 Create bunch of variable ports to capture actual values-

v_val1 = iif(v_pos1 =0,col1, SubStr( col1, 1, v_pos1 - 1))
v_val2 = iif(v_pos2 =0,'', SubStr( col1, v_pos1 + 1), SubStr( col1, v_pos1 + 1, v_pos2 - v_pos1 - 1))
v_val2 = iif(v_pos2 =0,'', SubStr( col1, v_pos2 + 1), SubStr( col1, v_pos2 + 1, v_pos3 - v_pos2 - 1))
...

• 3 Repeat above steps for column2 as well. Lets asseme they are v_val_col21,v_val_col22...
• 4 Once you have both set of columns, then concat both to get final values.

o_final = v_val1 ||'.' || v_val_col21 ||'||'|| v_val2 ||'.' || v_val_col22...