Null handling in transformer

[shalini11]

Hi All,

In the transformer, I have given a condition for a column as:

If Trim(NullToEmpty(Col1))='' Then 'Unknown'
Else Trim(NullToEmpty(Col1))

But the records having Col1 as blank are getting rejected. I dont want to reject the record, rather replace it with "Unknown" if it is blank.

The same condition is working for other 3 columns but not with column.

Can you pls help.

Thanks

[chulett]

And 'blank' means what here, exactly?

[shalini11]

Blank means that the value is not present in the input file.The file is tab delimited

[ray.wurlod]

Trim() will not remove all blank spaces, it will leave one. You may need to re-think your logic. For example

Code: Select all

NullToEmpty(Trim(InLink.TheField)) <= " "

[sureshreddy2009]

Hi
as you said the file has a Tab delimiter then the sequential file in datastage extracts the data from the file, if any value in file contains null then use this funtion to handle null values in transformer, if your aim is to replace null with 'unknown' then use this funtion If IsNull(Column) then 'Unknown'

in another case, if the field contains empty that is 1 or more spaces then use this funtion
If IsNull(Trim(column)) then 'unknown'

[naveen19]

Hi,

You can use this in ur derivation part..

If IsNull(DsLink12.INPUT_COLUMN) Then 'UNKNOWN' Else If Len(TrimB(DsLink12.INPUT_COLUMN)) =0 Then 'UNKNOWN' Else DsLink12.INPUT_COLUMN

Regards...Naveen.K

[ray.wurlod]

Ur was a city in ancient Babylon. The second person personal pronoun in English is spelled "your". Please maintain a professional standard of written English on DSXchange, which is used by many people whose first language is not English.

[shalini11]

Sorry to say but none of the above solutions are working. The records are still getting rejected. The condition is working fine for other fields. Only 2 fields are creating problem.

Please suggest what to do.

Thanks

[preetiv]

Hi,

What is the datatype of the column? Also, did you check the log to see if there is any info message about why the records are getting rejected?

Thanks,
Preeti

[shalini11]

Column is Varchar. In the log following error is displayed:

Xfm,0: Field 'BodyType' from input dataset '0' is NULL. Record sent to reject dataset.

[Swaruparani]

hi,

can u try this one:( NullToValue(DSLink3.sal,'Unknown'))

[samsuf2002]

Try making the column nullable and if it's an integer then make it varchar ....if it suits your requirement..... later do the nullhandling.

Null handling for integer will be ---> if Isnull(col1) = 0 then 'Unknown'......

[shalini11]

I have tried NullToValue. All the columns are varchar. It is not working.
Earlier I was giving "Null Field Value" in input Sequential file as '' then the records with nulls were getting rejected. So I changed the "Null Field Value" to null.Now records are not getting rejected but I am getting many warnings :
Xfm,0: Null string argument.

These warnings are much more than 50.Please help in getting rid of this warning.

I am not able to understand why the derivations are working by giving "Null Field Value" as null not ''. Can you pls help.

[samsuf2002]

Are those columns set as not nullable ? If yes then set them to nullable in seq file and transformer.

What is your job design ?
When you make it as nullable then edit the column and set the null field values = ''.

[shalini11]

The columns are nullable in seq file and transformer. In Seq file, in Formats tab, I have given the Null Field Value as '' but now I have changed it to null.