Page 1 of 1
lookup output values
Posted: Mon Jun 10, 2013 2:43 pm
by edward_m
i use lookup stage with constraint continue and i thought wherever the condition not met lookup output value coming in as null or empty string so i use the below condition in transformer stage to pass default value '~', But its not working.
Code: Select all
If IsNull(lookupoutcolumn) OR Trim(lookupoutcolumn)
Then '~'
Else lookupoutcolumn
Please advise how do we check lookup output values if they don't met the lookup criteria.
Thanks in advance.
Posted: Mon Jun 10, 2013 2:56 pm
by jwiles
What do you mean by "not working"? Failing? Not providing the expected result? Have you placed a Peek stage after the lookup to see what values you are receiving?
The output of the Trim() function is a string, not a boolean value.
Regards,
Posted: Mon Jun 10, 2013 7:27 pm
by edward_m
My input is
Col1
A
B
Lookup table
Col1. Col2
A 1
C 2
D 3
E 4
I need to get Col2 from lookup table based on key column Col1 input
Col1 Col2
A 1
B
I need to populate ~ for missing Col2 so in transformer stage I use the below code to assign ~ for Col2
If IsNull(Col2) or Trim(Col2)=''
Then '~'
Else Col2
When I looked at the output it's not showing Col2 '~' for Col1 B
Please advise where I am doing wrong.
Posted: Mon Jun 10, 2013 7:36 pm
by ray.wurlod
If the lookup fails and your Lookup Failed rule is "Continue", Col2 will be null - you don't have to check for "" unless Col2 = "" is a possible valid return value that you need to handle - this is not the case in your example.
Indeed, NullToValue(InLink.Col2, "~") should do it for you, provided that the data type of Col2 is a string of some kind.
Posted: Tue Jun 11, 2013 1:57 am
by edward_m
Yes,my lookup failure is continue.
Please advise why IsNull(Col2) is not returning true
Posted: Tue Jun 11, 2013 5:59 am
by boxtoby
I believe that by mixing IsNull and ColName=value in the same expression you get unexpected results.
As suggested earlier, just use the IsNull check, if you haven't already.
Cheers,
Bob.
Posted: Tue Jun 11, 2013 7:42 am
by edward_m
I am using OR condition so it should work.Anyhow i tried with only ISNull Comparison and it didn't work.
Posted: Tue Jun 11, 2013 8:03 am
by vinothkumar
Check the nullability and datatype of the field that came from lookup. If it is Integer and not null, 0 will be populated.
Posted: Tue Jun 11, 2013 8:11 am
by edward_m
Here are the properties for this field from lookup:
SQL TYPE:CHAR
Extended:unicode
Length:3
Nullable:yes
Posted: Tue Jun 11, 2013 8:33 am
by vinothkumar
Have you tried like this
If IsNull(Col2) or Len(Trim(Col2))=0
Then '~'
Else Col2