I have a field named CounterParty in my source file. What I need to do with this field is,
So how could I achieve this?
You could try something like
IIF(LTRIM(RTRIM(CounterParty) = '', NULL, CounterParty)
This shoud trim off any spaces in the string (if it is a string value) leaving it blank and then set the value to null and keep the original value if it is not blank.
Hi Isaac Niu,
In Expression Transformation check the column using IIF Condition. If the column value is Null Pass Null as it's value else the column value.
jaspreet you are correct but exclude the quotes.
Coming to null, null is a value neither smaller nor bigger and cannot be compared with any value either you cannot compare with null itself.
in informatica 'null' [null with quotes is treated as a string] and null is treated as actual null.
hope this may help you.
thanks and regards....
please read the question again..he meant to load 'null' as string otherwise there is no need to check using IFF ,he can directly map it to Target
Jaspreet there is noting personal in pointing at you.Isaac if your intention or what
you want was to get is 'null' as a string then jaspreet is absolutley correct by 100%,
and my info was for the newbies....please ignore if you think otherwise.
thanks & regards....
1. IIF(LENGTH(CounterParty)=0, null,CounterParty)
2. IIF(NOT ISNULL(CounterParty), CounterParty,null)
Please Find Below EXAMPLES for DATE conversion:
IIF( NOT ISNULL(MATURITY_DATE), TO_DATE(MATURITY_DATE,'DDMMYYYY'))
IIF( NOT ISNULL(DELIVERY_DATE), TO_DATE(DELIVERY_DATE,'DDMMYYYY'))
IIF( NOT ISNULL(MATURITY_DATE), TO_DATE(MATURITY_DATE,'DDMMYYYY'),null)
IIF( LENGTH(MATURITY_DATE)=8, TO_DATE(MATURITY_DATE,'DDMMYYYY'),null)
IIF( LENGTH(MATURITY_DATE)<>0, TO_DATE(MATURITY_DATE,'DDMMYYYY'),null)