IsNull, IsEmpty, IsInteger, IsString etc. returning -1 when True

Balders

Hi guys,

The formulae IsNull(), IsEmpty, IsString etc return -1 when true. I'd expect this to be 1 or 0 as a Boolean test.

Is this a bug? or is there logic behind this?

I've attached a workflow demoing some of these examples.

At the moment I am working around it with -IsNull([Value]) to return 1.

Cheers

Is formula Testing.yxmd

Preparation

Accepted answers

AdamR_AYX

So the difficulty is that there aren't any bool types within the formula tool. Everything is either a String, Double or Spatial Object. So bools are dealt with as doubles where zero is false and non zero is true. These particular formulas use -1 as their "non-zero value".

As you note using these as a bool works fine. Outputting to a bool field gives the expected result and if you were to nest them in another formula they would also work as expected.

e.g. iif(IsNull([Field1]), "a", "b")

I'm not sure I have a good answer as to why -1 was chosen over 1.

SeanAdams

I can't answer for why -1 was chosen in Alteryx. However, there is a reason that this happens in other programming language. The original reason that this happened is that in a binary number the left-most digit is often used for the sign of the number (0 being positive, 1 being negative). So when you have a bit-value, which by defintion 1 binary digit long, the value in position 1 is both the least significant digit and the most significant digit at once.

What this means is that if the value is zero, then it is 1, and because it has a 1 in the left-most spot it's treated as a negative value. So in a bit, where the machine treats it as a signed value, it can either be 0 or -1, it cannot ever be 1.

this has always tripped people up in C for years.

It's very quirky, but ties back to the way that negative numbers are represented in binary.

All comments

Federica_FF

Hi @Balders,

when you get -1 that usually means you're using the formula in the wrong way. 0=False, 1=True, -1=you're asking me something that makes no sense.

For example, if you have a null value in your column and you ask for a ISEMPTY, you're dealing with null values that can't be empty! That's why you get -1.

Pay attention in your example at the data type. Strings can't be integer, they are strings! Strings can't be null, strings should be empty, numeric fields can be null.

Balders

Hey Federica,

Thanks for the reply, I'm still not sure I understand.

Looking at the formula reference page IsEmpty suggests it includes testing for Nulls with its testing as per this:

' ISEMPTY(v) Tests if v is NULL or equal to "" or not '

from here: https://help.alteryx.com/9.5/Reference/Functions.htm

Am I missing something?

Balders

Adding on to this. If I output the field type as Boolean then I get TRUE which I can use a select tool to convert to a numeric field and get an output of 1. All other method's I've tried keeping it within the formula tool return -1 as shown below.

1 or -1.PNG

workflow attached as well.

Is formula Testing Extension.yxmd

MarqueeCrew

@Balders,

I don't have an official answer for you, but do understand your quandary. If the variable that you define is a data type of BOOL, then the -1 response becomes TRUE.

If Alteryx were to change that response, I'd imagine that folks who've used it could need to modify their workflows. I will tell a friend about the post and see if a better response is forthcoming.

Cheers,

Mark

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703