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 :)

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

workflow attached as well.

[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

[AdamR_AYX · Accepted solution]

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 · Accepted solution]

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.

[Balders]

Thanks Sean, that is a weird quirk of logic but sort of makes sense. It's always a relief to have one of those burning "but why?" questions answered.

[SeanAdams]

---

@SeanAdams wrote:

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.

---

Just realised that I had probably missed a cup of coffee and made an error in the explantion.

The second line should read:

Given that a bit can only be 1 or 0: 

• if the value is zero, then the least significant and most signifiant digits are both zero, so essentially the value is +0 (the sign digit is 0 meaning positive)  
• If the value is 1, then the sign digit is also 1 (meaning it's negative) so a 1 is treated as -1.    

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.