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
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 9:16 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hi, thanks for reading

Need to split a comma delimited record into rows.

If I have

field1,field2,field3

How can I end up with 3 records?

field1
field2
field3
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 38729
Location: Denver, CO
Points: 197743

Post Posted: Fri Feb 25, 2011 9:19 am Reply with quote    Back to top    

Horizontal Pivot.

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 9:21 am Reply with quote    Back to top    

Horizontal Pivot didn't work because it needs multiple columns. We have one field that contains

field1,field2,field3

How can we parse this one field and get 3 records

field1
field2
field3
Rate this response:  
Not yet rated
jwiles



Group memberships:
Premium Members

Joined: 14 Nov 2004
Posts: 1274

Points: 10406

Post Posted: Fri Feb 25, 2011 9:37 am Reply with quote    Back to top    

Parse the record into three columns when you read it, then pivot.

_________________
- james wiles


All generalizations are false, including this one - Mark Twain.
Rate this response:  
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 38729
Location: Denver, CO
Points: 197743

Post Posted: Fri Feb 25, 2011 9:42 am Reply with quote    Back to top    

If you had said "comma delimited field" rather than "comma delimited record" right up front, I would have expanded a bit more on my answer. Wink

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 9:46 am Reply with quote    Back to top    

Sorry it's a comma delimted field not a comma delimited record.

we won't know the number of comma seperated fields. We just know that we have a comma delimited field and each comma delimted value within the field should be parsed into it's own record.


field1,field,field3 etc...

will need to be
field1
field2
field3
etc...
Rate this response:  
Not yet rated
jwiles



Group memberships:
Premium Members

Joined: 14 Nov 2004
Posts: 1274

Points: 10406

Post Posted: Fri Feb 25, 2011 9:57 am Reply with quote    Back to top    

No worries.

Since there's not a fixed number of columns, some possibilities that come to mind:

- If you're using IS 8.5, a transformer could probably loop through the field and create the multiple records
- 8.1 or earlier: If there's a reasonable max number of columns in that field, you could use a transformer with constrained multiple outputs and funnel those together. Ugly but it works
- A BuildOp could handle this easily

Each of these actually do the pivot for you, so no need for a pivot stage.

Regards,

_________________
- james wiles


All generalizations are false, including this one - Mark Twain.
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: 38729
Location: Denver, CO
Points: 197743

Post Posted: Fri Feb 25, 2011 10:00 am Reply with quote    Back to top    

You could also continue down James' original path as long as you know what the max occurance would be. You then always generate that max number of fields, pivot out to the maximum number of records an ...

_________________
-craig

You may ask yourself, "Well, how did I get here?"
Rate this response:  
Not yet rated
jwiles



Group memberships:
Premium Members

Joined: 14 Nov 2004
Posts: 1274

Points: 10406

Post Posted: Fri Feb 25, 2011 10:25 am Reply with quote    Back to top    

I considered that as well...easy enough with the transformer. My reservation with it is the overhead of the extra records to be dropped. Depends on the input volume and the ratio of average population to max population.

If this is a one-time job, the overhead may be worth the easier design.

_________________
- james wiles


All generalizations are false, including this one - Mark Twain.
Rate this response:  
Not yet rated
DSguru2B

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 09 Feb 2005
Posts: 6854
Location: Houston, TX
Points: 35663

Post Posted: Fri Feb 25, 2011 11:24 am Reply with quote    Back to top    

An awk solution will be more dynamic with less overhead.

_________________
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Rate this response:  
Not yet rated
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 11:45 am Reply with quote    Back to top    

Thanks everyone for your ideas. Everyone was very helpful as usual. A person in our shop is good with PEARL, and is writing a program for what we need. We try not to do things outside of DataStage, but sometimes it makes sense to take another route.
Rate this response:  
Not yet rated
DSguru2B

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 09 Feb 2005
Posts: 6854
Location: Houston, TX
Points: 35663

Post Posted: Fri Feb 25, 2011 12:53 pm Reply with quote    Back to top    

Well, for awk, you could have put the one liner in the filter command of the sequential file stage. No external scripts required.

_________________
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Rate this response:  
Not yet rated
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 1:05 pm Reply with quote    Back to top    

One Liner - what's the one line?...if you don't mind.
Rate this response:  
Not yet rated
DSguru2B

Premium Poster


since February 2006

Group memberships:
Premium Members, Heartland Usergroup

Joined: 09 Feb 2005
Posts: 6854
Location: Houston, TX
Points: 35663

Post Posted: Fri Feb 25, 2011 1:07 pm Reply with quote    Back to top    

What column number is this in the entire record and how is the record delimited?

_________________
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Rate this response:  
Not yet rated
Marley777



Group memberships:
Premium Members

Joined: 27 Jan 2004
Posts: 311

Points: 2763

Post Posted: Fri Feb 25, 2011 1:22 pm Reply with quote    Back to top    

The input record conatins one field and it would be a varchar 255 field. The one input field contains value1,value2,value3 etc.. we will never know ahead of time how many comma delimited values the input field contains. So each value in the field would become it's own record.


value1
value2
value3
etc..
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