DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
esivaprasad
Participant



Joined: 09 Dec 2008
Posts: 135

Points: 1422

Post Posted: Wed Feb 14, 2018 7:01 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
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.

_________________
Siva
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42576
Location: Denver, CO
Points: 219102

Post Posted: Wed Feb 14, 2018 7:53 am Reply with quote    Back to top    

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? Wink

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
esivaprasad
Participant



Joined: 09 Dec 2008
Posts: 135

Points: 1422

Post Posted: Wed Feb 14, 2018 8:11 am Reply with quote    Back to top    

My source and target are both CSV file format.
Tranformer looping option not available in 8.1 version.


Thank you.

_________________
Siva
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42576
Location: Denver, CO
Points: 219102

Post Posted: Wed Feb 14, 2018 8:56 am Reply with quote    Back to top    

Right. Ancient. Wink

Still curious if a database is an option, if that's where your skills are - perhaps even an Oracle external table. I'd also be curious what you've tried so far.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 327

Points: 3307

Post Posted: Wed Feb 14, 2018 9:53 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 61
Location: France - Nantes
Points: 404

Post Posted: Wed Feb 14, 2018 11:17 am Reply with quote    Back to top    

You can do it using that logic:

Code:
                      -------------------                                       
                      | Sequential File |                                       
                      -------------------                                       
                               |                                               
-----------------     -------------------     ----------     -------------------
| Row Generator | --> |      Join       | --> | Filter | --> | Sequential File |
-----------------     -------------------     ----------     -------------------

Where:

- The Row Generator outputs the same number of rows as the max value of the “COUNT” column and generate the "ROWNUM" column containing the row number
- The input Sequential File read the input file
- The Join stage join the 2 sources using the “1=1” condition
- The Filter stage limits the output using the condition COUNT >= ROWNUM

Not the prettiest solution but that only use basic DataStage components

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42576
Location: Denver, CO
Points: 219102

Post Posted: Thu Feb 15, 2018 10:41 am Reply with quote    Back to top    

Unless I'm misremembering how things work (or just confused) I think you meant:

- The Filter stage limits the output using the condition COUNT <= ROWNUM

Yes?

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Thomas.B
Participant



Joined: 09 Apr 2015
Posts: 61
Location: France - Nantes
Points: 404

Post Posted: Thu Feb 15, 2018 11:17 am Reply with quote    Back to top    

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.

_________________
BI Consultant
Business & Decision
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42576
Location: Denver, CO
Points: 219102

Post Posted: Thu Feb 15, 2018 12:03 pm Reply with quote    Back to top    

Ah... guess I had the two values backwards in my head. D'oh.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
rkashyap



Group memberships:
Premium Members

Joined: 02 Dec 2011
Posts: 518
Location: Richmond VA
Points: 4661

Post Posted: Fri Feb 16, 2018 9:57 am Reply with quote    Back to top    

Another option could be leveraging Unix ... invoke following command in either External Source stage or using Filter option of Sequential file stage
Code:
awk 'BEGIN {FS="|";RS="\n"};  (NR> 1) {for(i=0; i<$2; ++i) {print $1}}' <inputfile>
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours