I have a file with data comming as 1,x1,x2,x3 2,a1,a2 3,b1,b2,b3,b4 1,y1,y2

I have a file with data comming as
1,x1,x2,x3
2,a1,a2
3,b1,b2,b3,b4
1,y1,y2,y3
2,c1,c2,c3
3,d1,d2

my out put should be as follows

x1,x2,x3,a1,a2,<null>,b1,b2,b3,b4
y1,y2,y3,c1,c2,c3,d1,d2,<null>

Please let me know how can we acheive this in informatica

Thanks in advance

Question Posted / sri

4 Answers
7865 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

I have a file with data comming as 1,x1,x2,x3 2,a1,a2 3,b1,b2,b3,b4 1,y1,y2,y3 2,c1,c2,c3 3,d..

Answer / siva

Import the file with comma delimited, so that, it creates
with 5 columns c1,c2,c3,c4,c5
take the mapping as follows:
SQ --> Exp -->filter--> target_
in the expression take a variable port and assign the value
as iif(c1 = 1,concatenate all the c2,c3,c4,c5 otherwise
concatenate varibale post value with c2,c3,c4,c5)
then you will get as follows:
c1, v_p
1, 'x1,x2,x3,<null>'
2, 'x1,x2,x3,<null>,a1,a2,<null>,<null>'
3, 'x1,x2,x3,<null>,a1,a2,<null>,<null>,b1,b2,b3,b4'
1, 'y1,y2,y3,<null>
2, 'y1,y2,y3,<null>,c1,c2,c3,<null>
3, 'y1,y2,y3,<null>,c1,c2,c3,<null>,d1,d2,<null>,<null>'

you can lso ingnore the unncessary columns where ever

filter out only c1 = 3 to the target

It helps you to succeed..

Is This Answer Correct ?

1 Yes

0 No

I have a file with data comming as 1,x1,x2,x3 2,a1,a2 3,b1,b2,b3,b4 1,y1,y2,y3 2,c1,c2,c3 3,d..

Answer / pandu

u can use sq transformation .
start value 1,
incremental value 1,
reset value:slect,
and use the rank transformation.

Is This Answer Correct ?

1 Yes

0 No

I have a file with data comming as 1,x1,x2,x3 2,a1,a2 3,b1,b2,b3,b4 1,y1,y2,y3 2,c1,c2,c3 3,d..

Answer / rahul

use an exp t/r,

take an variable port as count = 0
keep inc the count by 1 and place a conditin if count > 4 ,
than set count = 0

and in other variable port ypu concate the columns value.

Is This Answer Correct ?

1 Yes

2 No

I have a file with data comming as 1,x1,x2,x3 2,a1,a2 3,b1,b2,b3,b4 1,y1,y2,y3 2,c1,c2,c3 3,d..

Answer / bebo

use comma delimiter..infa will identify the null values
while importing the flat file

Is This Answer Correct ?

0 Yes

2 No

Post New Answer