How to generate multiple rows?

[anu123]

Helo every one.

I have a scenario as below...

input(a FILE):

col1 col2 col3
---------------------
100 aa1 bb1
200 aa2 bb2

Output needed as (also a FILE)

col1 col2 col3 col4 col5
----------------------------
100 aa1 bb1 xx1 yy1
100 aa1 bb1 xx2 yy2
100 aa1 bb1 xx3 yy3
200 aa2 bb2 xx1 yy1
200 aa2 bb2 xx2 yy2
200 aa2 bb2 xx3 yy3

Any ideas how this can be accomplished...?

thanks in advance.

[kcbland]

Sort the data, use Stage variables to track group changes and reset the numbering sequences.

[DSguru2B]

How are you getting xx1 and yy1. How do you know you need to make 3 copies of each row. If that remains static, feed the input file the transformer with three output links, add two columns, duplicate the first three columns, add appropriate value for fourth and fifth column in the three links. Concatenate the files together.

[anu123]

Thank you all for your time.

each row may be needed to copy more than thrice depending on case.But based on the case 'Number of Copies' I need to make is static(i.e 3/10/50..).

If its '3', having 3 streams to feed 3 transformers sounds good. But if its '50', we probably may need better approach.

'xx1' and 'yy1' are sample values i took. But really they look some thing different.So I can not go for incrementing a number.But these values are static even in case of '50' copies..


thanks again...

How are you getting xx1 and yy1. How do you know you need to make 3 copies of each row. If that remains static, feed the input file the transformer with three output links, add two columns, duplicate the first three columns, add appropriate value for fourth and fifth column in the three links. Concatenate the files together.

[kumar_s]

Hi Anu,
Again where you get these case from? Perhaps you havent mentioned here.
There are several other ways of acheiving this. viewtopic.php?t=88639 You can create a parallel routine as well.

[dsisbank]

sort data using col1 ,and set true Create Key Change Column property.DS adds one more column Keychange().Use transformer stage variables (IF Keychange()=1 THEN 1 ELSE variable+1 ),concanacate xx

[DSguru2B]

Need actual sample data with explanation of how each case is derived.

[anu123]

source :

100 DAVE YAHOO
200 ANN MS
300 SCOTT ORACLE

output

100 DAVE YAHOO 1 CHRONIC
100 DAVE YAHOO 2 HELTHY
100 DAVE YAHOO 3 CORDIAC
200 ANN MS 1 CHRONIC
200 ANN MS 2 HELTHY
200 ANN MS 3 CORDIAC
300 SCOTT ORACLE 1 CHRONIC
300 SCOTT ORACLE 2 HELTHY
300 SCOTT ORACLE 3 CORDIAC

in this case below are static.

1 CHRONIC
2 HELTHY
3 CORDIAC
I need to make 3 copies of each incoming row.

thanks

Need actual sample data with explanation of how each case is derived.

[chulett]

Me, I'd create a table with the three values in my database of choice, load the input file into another and then extract from that database letting the sql do a cartesian product.

[ray.wurlod]

Or a Lookup from a three row table, allowing multiple row return. Create a Lookup File Set with "X" as the key for all three columns (you will need to set "allow duplicates" to true). Then lookup against the key value "X", with the drop down list allowing multiple row returns from that reference link. Use Entire partitioning for the Lookup File Set.

[chulett]

Or that.

[anu123]

Thank you every one for your valuable time and suggessions.I will update you all once I am done with that.

thanks again..

Or that.