A different Requirement

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
lna_dsuser
Premium Member
Premium Member
Posts: 29
Joined: Tue Aug 14, 2012 1:06 pm

A different Requirement

Post by lna_dsuser »

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
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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 selectively remove columns from the stream that have specific values. But how about column naming and should each row (assuming the file gets more than one row) have a different number of columns?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
lna_dsuser
Premium Member
Premium Member
Posts: 29
Joined: Tue Aug 14, 2012 1:06 pm

Post by lna_dsuser »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

(see previous answer)
-craig

"You can never have too many knives" -- Logan Nine Fingers
lna_dsuser
Premium Member
Premium Member
Posts: 29
Joined: Tue Aug 14, 2012 1:06 pm

Post by lna_dsuser »

Craig

Can you pls elaborate your answer i didnt got it
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
jerome_rajan
Premium Member
Premium Member
Posts: 376
Joined: Sat Jan 07, 2012 12:25 pm
Location: Piscataway

Post by jerome_rajan »

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

Code: Select all

+----+               +----+                           +------------+
| 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
Data Integration Consultant at AWS
Connect With Me On LinkedIn

Life is really simple, but we insist on making it complicated.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That's close to what I was thinking. :wink:

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 trim off the 'extra' trailing delimiter you'd get from concatenating the columns and delimiters together:

Code: Select all

A,B,C,D,E
0,5,4,0,1

A: 
B: 5,
C: 5,4,
D: 5,4,
E: 5,4,1,

Last step: 5,4,1
-craig

"You can never have too many knives" -- Logan Nine Fingers
bondlee
Participant
Posts: 7
Joined: Tue Jan 11, 2011 11:15 pm

Post by bondlee »

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):
viewtopic.php?p=443641#443641
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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 stage, so perhaps a horizontal pivot? Or a funnel to bring the two rows together.
-craig

"You can never have too many knives" -- Logan Nine Fingers
srinivas.nettalam
Participant
Posts: 134
Joined: Tue Jun 15, 2010 2:10 am
Location: Bangalore

Post by srinivas.nettalam »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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 (,) .
Post Reply