looping a record

[c341]

Hi
I like to loop a record on certain number of times based on the
incoming record value.
For Example, If my record has a value 5, I need to get that record 5
times.
Based on this I'm doing some calculations on the 5 records.
How to implement this.
Actually there are lot of records, the value have maximum of value
"30". In this case I have get that same record 30 times.
Thank You

[kcbland]

I see two options, one easy, the other scalable.

Easy solution: Put N output links from a transformer to a link collector or a series of files. Each link has a constraint that only allows the row if its fixed value is less than your looping value.

Scalable solution: Load your source data into a hash file, as well as output a file of just the key value and your looping value. Load that file into a table. Write a job that selects from that table and does a cartesian product self-join to produce output rows of the key value and 1 to your looping value for that key and order by that looping value, now just an incrementing number. Stream that output and reference your hash file to get the rest of the data referenced back into the data stream.

[prabu]

Hi
I like to loop a record on certain number of times based on the
incoming record value.

have a routine and do you calcuations there. getting/fecthing the same records <b>5/n</b> times is a costly operation. if you can explain bit more of what you are trying to do , it will help to suggest a solution

[c341]

Example:
There are 2 columns lets say Col1 and Col2 and it has values as below.

Col 1 Col2
5 10

I want to break this row into 5 rows (value of the Col1), and the Col1 should be decremented and Col2 should have a value Col2 / Col1 ie..10/5 as below.

Result:

Col1 Col2
5 10/5
4 10/5
3 10/5
2 10/5
1 10/5

Thank You

[c341]

Hi Kenneth Bland

Scalable solution: Load your source data into a hash file, as well as output a file of just the key value and your looping value. Load that file into a table.

....Upto this point its OK.

Write a job that selects from that table and does a cartesian product self-join to produce output rows of the key value and 1 to your looping value for that key and order by that looping value, now just an incrementing number. Stream that output and reference your hash file to get the rest of the data referenced back into the data stream.
I can make a join and produce a key value and don't understand " 1 to your looping value for that key"....where to provide this...I'm unclear here..

[kcbland]

Here's some SQL to demonstrate a way to do what you want:

Code: Select all

create table kcb_values (increasing_nbr  integer);

insert into kcb_values (select rownum from all_tables where rownum <= 100);

create table kcb_work (your_key integer, max_nbr integer);

insert into kcb_work (select rownum, mod(rownum,5)+5 from all_tables where rownum <= 100);


select your_key, max_nbr, increasing_nbr from kcb_work, kcb_values
where increasing_nbr <= max_nbr
order by your_key, increasing_nbr

Now, I created a table to simply use to join against so that I can get a cartesian product. The kcb_work table contains your key value plus the max nbr. The query demonstrates that joining the work table to the value table creates a cartesian product result set returning 1 to max_nbr rows for each key.

[ailuro]

c341, you can also try to...

Read the input sequential file in BASIC using OpenSeq and ReadSeq.
For every record, pass Col1 and Col2 as parameters to a job which generates the Col1 number of rows using a transformer's stage variable.
In the same stage, derive the new Col2 by Col2 : "/" : Col1.
Append the generated records to an output sequential file.

Code: Select all

        |               +----+
     +--+\              | -- |
     +--+/------------->| -- |
        |               +----+
      xform              seqf

Not recommended for large data though.

[Viesuls]

It's easy to make a plug-in that will do record looping. Specialized plug-in is most elegant way to solve this problem. Take a look at Active Plug-In sample form DSDK.

[ogmios]

It's easy to make a plug-in that will do record looping. Specialized plug-in is most elegant way to solve this problem. Take a look at Active Plug-In sample form DSDK.

Same advice as with writing SAP extensions... don't. The software may change and you may be in big trouble with your custom functionality, which is now broken.

This would look more like a regular BASIC job to me.

Ogmios

[tonystark622]

Another thing you might consider:

Write the data to a hash file. Add a unique key field (use @Outrownum as the value) and another field, F2. The derivation for F2 is a function (pass in Count as a parameter) (that you have to write) that returns a string of values from 1 to count, separated by @VM chars. When reading from this hash file, in the metadata for F2, set "Type" to "MV" (Association should go to F2), then, at the top, set "Normalize On" to F2. Now when you read the data from the hash file you'll get one row for each value in the F2 field. You can drop the key and F2 after reading from the hash file, if you so desire.

Quick, easy code and easy to implement.

Good Luck,
Tony

[ogmios]

It's easy to make a plug-in that will do record looping. Specialized plug-in is most elegant way to solve this problem. Take a look at Active Plug-In sample form DSDK.

Same advice as with writing SAP extensions... don't. The software may change and you may be in big trouble with your custom functionality, which is now broken.

This would look more like a regular BASIC job to me.

Ogmios

[mhester]

I think you had better stick with Ken's solution and not undertake a custom active or passive plugin. The followng is misleading -

It's easy to make a plug-in that will do record looping. Specialized plug-in is most elegant way to solve this problem. Take a look at Active Plug-In sample form DSDK.

Unless you are proficient in C and understand the DS API very well, this could turn out to be an excersize that may/may not work and would ultimately cost your company/customer more than implementing a solution like Ken outlined.

And if you were going to expose a custom GUI rather than the one supplied for active and passive stages then you need to add C++ and COM to the list of skills.

Regards,