Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Balders
11 - Bolide

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

8 REPLIES 8
Federica_FF
11 - Bolide

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
11 - Bolide

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
11 - Bolide

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.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
AdamR_AYX
Alteryx Alumni (Retired)

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.

Adam Riley
https://www.linkedin.com/in/adriley/
SeanAdams
17 - Castor
17 - Castor

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
11 - Bolide

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
17 - Castor
17 - Castor

 


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

 

Labels