Page 1 of 1
looping a record
Posted: Fri Oct 29, 2004 10:13 pm
by 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
Posted: Fri Oct 29, 2004 11:10 pm
by 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.
Re: looping a record
Posted: Sun Oct 31, 2004 3:42 am
by prabu
c341 wrote: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
Posted: Mon Nov 01, 2004 9:01 pm
by 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
Posted: Mon Nov 01, 2004 10:09 pm
by 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..
Posted: Mon Nov 01, 2004 10:54 pm
by 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.
Posted: Tue Nov 02, 2004 1:54 am
by 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.
Posted: Tue Nov 02, 2004 5:45 am
by 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.
Posted: Tue Nov 02, 2004 7:44 am
by ogmios
Viesuls wrote: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
Posted: Tue Nov 02, 2004 7:44 am
by 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
Posted: Tue Nov 02, 2004 7:51 am
by ogmios
Viesuls wrote: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
Posted: Tue Nov 02, 2004 1:58 pm
by 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,