Filter with empty value and equality operator

1

Hi all,
I can’t understand why it is so difficult to apply a filter on numeric column with empty values, probably there is something I lost…

This is my first condition:

image
image871×307 20.6 KB

Then I tried with:
image

The only way I found is:
[EC1]=0 OR [EC1]=1 OR [EC1]=2 OR [EC1]=3 OR [EC1]=4 OR [EC1]=5

But it seems very strange to me it is the only way…

Could you please help me and clarify how to manage empty values in expressions with operators?

Thank you
Claudio

2

An empty value is not comparable to a number by design. This helps detect unexpected empty values. However, because of that, a filtering condition must do type-checking first. Here are examples of correct expressions for your case:

IF not isempty([EC1]) THEN [EC1] <= 5 ELSE false()

Alternatively

if( isempty([EC1]), false(), [EC1] <= 5)

If you only need to select a few integers, I would suggest using the simple “Filter” action.

3

I understand the reason and the solution, but it makes in complex conditions the formular more complex.

Maybe you can add it on the feature request list @dgudkov to have an option checkbox for the filters like "interpret empty values ​​as 0" and/or "interpret non numeric values ​​as 0".

hint for others - my workaround to keep the formular as simple as possible is a previous modify step, that sets non numeric values to 0. (but I would also be happy, if I can leave out this).

best wishes
Peter

4

may try with coalesce

coalesce([EC1],0)<5

or

[EC1]+0<5

1 Like
5

That's a good solution.

6

Treating all empty values as 0s is risky - you may overlook an error.

If you're 100% sure that in a certain column, empty values should be treated as 0s, I'd suggest using one of our actions to replace empty values with zeroes. For instance, the "Replace" action.

If you don't want to create or modify a column, you can add a LET expression with the coalesce() function right before your formula. For instance:

LET a = coalesce([A],0)
a + [B] < a - [C]