Multiple records based on value in column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 am
Multiple records based on value in column
Hi ,
we are using datastage 8.1.
my requirement is :
Input is like this
customer|Count
A|4
B|1
C|2
Based on customer count value , need to generate that many output columns for the same customer.
output required as
Column1 | Column2
A|1
A|1
A|1
A|1
B|1
C|1
C|1
please help me how to get output as requested format.
Thank you.
we are using datastage 8.1.
my requirement is :
Input is like this
customer|Count
A|4
B|1
C|2
Based on customer count value , need to generate that many output columns for the same customer.
output required as
Column1 | Column2
A|1
A|1
A|1
A|1
B|1
C|1
C|1
please help me how to get output as requested format.
Thank you.
Siva
Well... first thing I would have suggested was 'transformer looping' but I'm pretty sure it's not available in your ancient version. What is the source - a file, a database table or something you are willing to load into a table? Thinking there's a SQL solution available if so. Or perhaps someone else has a clever solution... perhaps something written in C++ by UCDI? Command line filter Perl shenanigans?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 135
- Joined: Tue Dec 09, 2008 10:35 am
Im open to C if you need it, but I would personally attack this one in excel if at all possible (to the point that I might even consider elimination of datastage entirely depending on the circumstances).
Another idea.. .what is the largest # that can appear? If its less than 10, you can just split out a transformer and manufacture up to that many rows simply. A little fragile if the # can get big in the future, but sometimes, you know it won't...?
I have found that the VB transformer using a VB routine is pretty much equally fast to C for simple tasks. C is marginally faster on encrypt or bit manipulation or hard math, but string processing, its a wash.
Another idea.. .what is the largest # that can appear? If its less than 10, you can just split out a transformer and manufacture up to that many rows simply. A little fragile if the # can get big in the future, but sometimes, you know it won't...?
I have found that the VB transformer using a VB routine is pretty much equally fast to C for simple tasks. C is marginally faster on encrypt or bit manipulation or hard math, but string processing, its a wash.
We need to send the record to the output unless the row number is higher than the number of rows to generate, in my head, my logic is right.
Anyway, Siva will test it if he decides that is the right solution.
Anyway, Siva will test it if he decides that is the right solution.
BI Consultant
DSXConsult
DSXConsult
Another option could be leveraging Unix ... invoke following command in either External Source stage or using Filter option of Sequential file stage
Code: Select all
awk 'BEGIN {FS="|";RS="\n"}; (NR> 1) {for(i=0; i<$2; ++i) {print $1}}' <inputfile>