Concatenation Problem, Urgent

[steve009]

I have a source file

100,a, ,c
101,b, ,a
102,a,b,
103, ,b,c

I want the out put to be

100 ac
101 ba
102 ab
103 bc

when i use the concatenate ':' and when it has null values in the middle of the columns the output is coming as null values.

[chucksmith]

So, you have defined your input file as four comma-delimited columns:
in1
in2
in3
in4

Your output is one column with a derivation like:

Code: Select all

(If isnull(in1) Then "   " Else in1) : " " : (If isnull(in2) Then "" Else in2) : (If isnull(in3) Then "" Else in3) : (If isnull(in4) Then "" Else in4)

[phanee_k]

Hi,
Use NulltoEmpty tranform while concatenating the fields.
It will work.

Ex
NulltoEmpty(Field1):NullToEmpty(Field2):NullToEmpty(Field3):NulltoEmpty(Field4).

Phani

[Lanover]

Steve,
In any expression that contains a null, DataStage will always return a null. Thus if one or more fields are null in a concatenation or arithmetic expression then the result will be null. Use stage variables to handle any nulls on your input file, setting the stage variable to a computable field if the field is null, then use the stage variables in your concatenation. Only use the IsNull function, do not check for = '' as this is not handled as a null by DataStage.

I have a source file

100,a, ,c
101,b, ,a
102,a,b,
103, ,b,c

I want the out put to be

100 ac
101 ba
102 ab
103 bc

when i use the concatenate ':' and when it has null values in the middle of the columns the output is coming as null values.