Null handling in transformer
Moderators: chulett, rschirm, roy
Null handling in transformer
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)) <= " "
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 62
- Joined: Sat Mar 07, 2009 4:59 am
- Location: Chicago
- Contact:
Use another logic
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'
Suresh Reddy
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
ETL Developer
Research Operations
"its important to know in which direction we are moving rather than where we are"
Re: Use another logic
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 5
- Joined: Wed Sep 12, 2007 1:54 am
- Location: Bangalore
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
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.
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.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse