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.
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")
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")