Hi ,
I have a equirement to generate number of duplicate records based on a column value .Example is shown below as :
Input :
id,name,address,col1
1,abc,xyz,5
2,cde,yz,2
Output:
As col1 is having value as 5 ,so in output we require 5 nstances of the same record .Similarly for second record ,we need to generate 2 records
id,name,address,col1
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
2,cde,yz,2
2,cde,yz,2
Please advise how can i achieve this result .
Generating records based on column value
Moderators: chulett, rschirm, roy
This looks like the perfect application of transform stage loops available in Version 8.7 and above of DataStage. Do you have this version?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Generating records based on column value
So... you need to turn the '5' in the original record to a '10' for that first id?bgs_vb wrote:id,name,address,col1
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
1,abc,xyz,10
2,cde,yz,2
2,cde,yz,2
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Who told You that using loop variables was relevant in terms of performance? It is not. By the way, I thought using transform loop variables is not an option because You are on version 8.1?bgs_vb wrote: As we are expecting huge data from source and using transform loop variables will slow down the process .
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
sorry, as corrected above, the loops are available as of 8.5.
Since you have 8.1 and loops within a transform stage are not an option you have a simple, but limited option if the maximum number of iterations is known; i.e. if you know you won't have more than 25 in column1 then you could make a transform stage with 25 outputs that have conditions along the lines of "In.Column1 < 2", "In.Column3 < 1", etc. This is a simple and bulletproof approach but won't work if can have values of 100 or higher.
Since you have 8.1 and loops within a transform stage are not an option you have a simple, but limited option if the maximum number of iterations is known; i.e. if you know you won't have more than 25 in column1 then you could make a transform stage with 25 outputs that have conditions along the lines of "In.Column1 < 2", "In.Column3 < 1", etc. This is a simple and bulletproof approach but won't work if can have values of 100 or higher.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: