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 :)
Solved! Go to Solution.
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.
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?
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.
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
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.
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.
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 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:
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.