lookup output values

[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.

[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,

[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.

[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.

[edward_m]

Yes,my lookup failure is continue.
Please advise why IsNull(Col2) is not returning true

[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.

[edward_m]

I am using OR condition so it should work.Anyhow i tried with only ISNull Comparison and it didn't work.

[vinothkumar]

Check the nullability and datatype of the field that came from lookup. If it is Integer and not null, 0 will be populated.

[edward_m]

Here are the properties for this field from lookup:
SQL TYPE:CHAR
Extended:unicode
Length:3
Nullable:yes

[vinothkumar]

Have you tried like this

If IsNull(Col2) or Len(Trim(Col2))=0
Then '~'
Else Col2