DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
lna_dsuser



Group memberships:
Premium Members

Joined: 14 Aug 2012
Posts: 29

Points: 387

Post Posted: Wed Jul 03, 2013 10:53 pm Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Hi All,

I have a CSV file with only 1 row containing 10 columns .

all the columns have data type as Integer.

My requirement is to pass only the columns in the target CSV file having value not equal to zero.
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Wed Jul 03, 2013 11:06 pm Reply with quote    Back to top    

I'm not quite sure what your requirements really are; but text files need to be read line-by-line, so the reader sequential stage will read the whole line with all of the columns. After that, one can ...

_________________

Image
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Jul 03, 2013 11:06 pm Reply with quote    Back to top    

Write the output record as one string field and craft it in a transformer from the appropriate source columns. Concatenate them together with the appropriate field delimiter between them.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
lna_dsuser



Group memberships:
Premium Members

Joined: 14 Aug 2012
Posts: 29

Points: 387

Post Posted: Wed Jul 03, 2013 11:13 pm Reply with quote    Back to top    

Let me clarify more

Source contains only 1 row with 5 columns
A,B,C,D,E
0,5,4,0,1

Want output as whose value are not Zero

B,C,E
5,4,1
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Jul 03, 2013 11:21 pm Reply with quote    Back to top    

(see previous answer)

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
lna_dsuser



Group memberships:
Premium Members

Joined: 14 Aug 2012
Posts: 29

Points: 387

Post Posted: Wed Jul 03, 2013 11:52 pm Reply with quote    Back to top    

Craig

Can you pls elaborate your answer i didnt got it
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Thu Jul 04, 2013 7:06 am Reply with quote    Back to top    

Which part is unclear? Build the output record yourself. Leave out the zero values. Write it out as one long string. Ask specific questions if you still need help.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
jerome_rajan



Group memberships:
Premium Members

Joined: 07 Jan 2012
Posts: 376
Location: Piscataway
Points: 2955

Post Posted: Fri Jul 05, 2013 2:51 am Reply with quote    Back to top    

To build on Craig's answer, this is what you could do


Code:

+----+               +----+                           +------------+
| seq|--------------->|Xfm|--------------------------->|Col Import |------>Target
+----+               +----+                           +------------+

Read as          Use field function               
1 column       and check if value is 0.
                    If no, append to delimited
                    string else do nothing

_________________
Jerome
Senior Consultant At Deloitte
Connect With Me On LinkedIn

Life is really simple, but we insist on making it complicated.
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Fri Jul 05, 2013 7:16 am Reply with quote    Back to top    

That's close to what I was thinking. It doesn't need to be read as one column so much as written out that way. And I don't think the Column Import stage is needed. As the final step I would tr ...

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
bondlee
Participant



Joined: 11 Jan 2011
Posts: 7

Points: 66

Post Posted: Sun Jul 07, 2013 9:30 am Reply with quote    Back to top    

You can try doing it in Unix (assuming that your environment is unix). an awk script can brilliantly come to your rescue. But if you need column names, that will be complex as the selection of columns is dynamic here.

You can alternatively use Craig's solution which has been discussed in one of my posts (link below):
http://www.dsxchange.com/viewtopic.php?p=443641#443641
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Sun Jul 07, 2013 9:41 am Reply with quote    Back to top    

I'm thinking you would handle the column names the same way... dynamically paired with the matching non-zero values. It would need to be written out as a row of data rather than automatically by the s ...

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
srinivas.nettalam
Participant



Joined: 15 Jun 2010
Posts: 134
Location: Bangalore
Points: 1030

Post Posted: Wed Jul 10, 2013 1:07 am Reply with quote    Back to top    

Does your source always have 1 row or it can have multiple rows too? In that case how do you want your column names to be populated?

_________________
N.Srinivas
India.
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: 43085
Location: Denver, CO
Points: 222463

Post Posted: Wed Jul 10, 2013 6:48 am Reply with quote    Back to top    

lna_dsuser wrote:
Source contains only 1 row

I'm assuming that means always. The required output won't make much sense with multiple rows.

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Wed Jul 10, 2013 10:55 am Reply with quote    Back to top    

You can read the one row as a number of rows ( with only one column of type integer ) if your Record Delimiter property is set to Comma, in the transformer , pass only rows where integer data is not zero ( Using a transfomer constraint Or Filter stage Where clause) and write this to another file with record delimiter as a Comma .
For both file stages ( source and target) set the field delimiter and quote character to 'none'

To Access record Delimiter property , go to Format tab of the sequential file, and choose the Record Level property folder node, change the record delimiter from Unix Newline to Comma (,) .
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