Page 1 of 1
copy rows based on input column value!
Posted: Fri Feb 06, 2009 11:56 am
by samplify
Hi,
I have a curious problem which didn't turn up in DSXchange search.
i have a key column and a count column in the source, need to produce "as many" output rows for "that key column" in the source.
Sample:
(input)
Key/Count
1,2
2,1
3,5
4,2
(output)
Key/Count
1,2
1,2
2,1
3,5
3,5
3,5
3,5
3,5
4,2
4,2
Thanks in advance! (and hope its not a UNIX solution!)
Sam
Posted: Fri Feb 06, 2009 2:24 pm
by kandyshandy
This could not be done directly in a PX job but could be done in a server / BASIC routine or px C++ routine
Here is the logic
Open your input file
Loop start here
Read first line to a Linevariable
Assign second column value to a countvariable
Open Output file
For countvariable =1, increment by 1
Write Linevaraible to output file
Next
Next line in Loop
Close both files.
Posted: Fri Feb 06, 2009 3:19 pm
by ray.wurlod
One question.
Why?
@Ray
Posted: Sat Feb 07, 2009 4:59 pm
by samplify
Well, the requirement wants the evaluation of an expression (Field1+(Field2*n))
where n is (0 to (Field3)-1)
so i was wondering if i split row into as many rows as Field3 value minus 1 then i can straight away use the formula at a "scalar" level.
(oh yes and i need that many multiple rows being written to the output)
Generating Rows Using a Multiplier
Posted: Sun Feb 08, 2009 1:03 am
by ray.wurlod
samplify wrote:(oh yes and i need that many multiple rows being written to the output)
I still don't understand why (that is, how any such output could be useful/meaningful). Maybe it's just me.
Posted: Thu Mar 26, 2009 6:02 am
by jusami25
Hi,
he had the same requeriment and we solved it with two jobs:
First job
Input Record --> KEY, COUNT
Seq File --> Transform -- Seq File
On the Transfrom job we use the Str() function in this way:
If InputLink.COUNT > 0 Then
InputLink.KEY : ',' : InputLink.COUNT: ',' : Str(Char(10) : InputLink.KEY : ',' : InputLink.COUNT, InputLink.COUNT-1)
Else ''
We map this derivation to a single field with enought lenght to store all the records generated for each input row.
Output Record --> ALL_RECORDS
Second job
Seq File --> Transform -- Seq File
This job will use the output file from the First job as input with the next format:
field delimiter = ,
record delimiter = Char(10)
Input Record --> KEY, COUNT
At this point we have the number of records we need and on the transform we can perform the required operations...
Key points here are the Char(10) Return code, the Str function to repeat the fields dinamically and the format on which we read the file on the second job.
Posted: Thu Mar 26, 2009 7:49 am
by DSguru2B
Or, maybe a three to four liner shell script.....
Re: copy rows based on input column value!
Posted: Thu Mar 26, 2009 2:24 pm
by ray.wurlod
samplify wrote:Thanks in advance! (and hope its not a UNIX solution!)
I'd be inclined to write a before-job subroutine (or just a routine to be invoked from a Routine activity in a sequence).