How to check if value is not null in a comparison?

[Julia1231]

Hi,

I want to check if all the value (from different fields) are < a, it will mark as yes.

If one of them > a, it will be "no". 

Knowing that it's not always have 3 values (some id has only value1 or (value1 and value2)), this eval will give nothing in the result.

|eval test=if(value1<a and value2<a and value3<a, "yes", "no")

 I'm searching for a way to take into account only when a value is not null.

|eval test=if(isnotnull(value1)<a and isnotnull(value2)<a and isnotnull(value3)<a, "yes", "no")

but I have this error: Error in 'eval' command: Type checking failed. The '<' operator received different types.

[yuanliu]

I think you just need a logic to handle cases when a field is null.  According to your description, isnull(anyvalue) would go with "yes".  Hence,

| eval test=if((isnull(value1) OR value1<a) and (isnull(value2) OR value2<a) and (isnull(value3) OR value3<a), "yes", "no")

 This is assuming that at least one of value1, value2, and value3 always exists.  If you want to assign "no" to cases where all 3 fields are null, add one more condition:

| eval test=if((isnull(value1) OR value1<a) and (isnull(value2) OR value2<a) and (isnull(value3) OR value3<a) AND isnotnull(coalesce(value1, value2, value3)), "yes", "no")

[ITWhisperer]

You haven't said whether a null value is good or not

|eval test=if((isnotnull(value1) and value1<a) and (isnotnull(value2) and value2<a) and (isnotnull(value3) and value3<a), "yes", "no")

or

|eval test=if((isnull(value1) or value1<a) and (isnull(value2) or value2<a) and (isnull(value3) or value3<a), "yes", "no")