Need Repetition of rows

moulipx · Post by **moulipx** » Wed Jan 12, 2011 9:35 am

Hi All,

I need your help in implementing the below requirement in datastage

Iam having 3 columns in a sequential file with comma delimeter.And i need to write into one sequentail file..

And my testdata is ::

1A,aaa,1
2A,bbb,2
3A,cccc,3
4A,ddd,1

And i need to produce the ouput based on third column value.If the third column value contains 1 i need one copy of the row.If it contains 2 i need 2 copies of that row..
1A,aaa,1
2A,bbb,2
2A,bbb,2
3A,cccc,3
3A,cccc,3
3A,cccc,3
4A,ddd,1

Thanks in advance

DSguru2B · Post by **DSguru2B** » Wed Jan 12, 2011 9:45 am

Put the following in the filter command of the sequential file stage

Code: Select all

awk -F"," '{for (i=1; i<=$3; i++) {print $0}}'

moulipx · Post by **moulipx** » Wed Jan 12, 2011 10:11 am

Thanks for your reply.

Can we implement this logic in transformer?

DSguru2B · Post by **DSguru2B** » Wed Jan 12, 2011 10:13 am

Yes you can. You will have to cook up a custom C routine that you can call from within the transformer. The C routine will do exactly what my awk script is doing, strip out the third column and run a loop to duplicate the row that many number of times.

chulett · Post by **chulett** » Wed Jan 12, 2011 10:23 am

... or you could upgrade to 8.5 where they've implemented "transformer looping".

DSguru2B · Post by **DSguru2B** » Wed Jan 12, 2011 3:47 pm

So transformer looping is finally here. That is so cool.

vmcburney · Post by **vmcburney** » Wed Jan 12, 2011 5:03 pm

Prior to version 8.5 the lookup stage is one of the easiest ways to multiple or clone input rows.

Create a text file with the following values:
Key
1
2
2
3
3
3
4
4
4
4
etc

Join to this in a lookup stage using the Key field linked to the input counter field and set the combo box property to make this a multi value lookup. This will give you multiple output rows based on the number of lookup rows found.