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 Knowledge Base

Definitive answers from Designer Desktop experts.

Boolean versus Positional Results

CristonS
Alteryx Alumni (Retired)
Created

Historically, most Boolean return values are 0=False and 1=True. Officially, Alteryx uses 0 for False, and -1 for True (yes, that's negative one), with the note that any value other than 0 would indicate the value is True.
 

The Contains and StartsWith/ EndsWith functions are Boolean, whereas Findstring is positional. This means that when using Findstring, the results are positions, not Boolean values. So, if your field does not contain your search value, it will return a "-1" which means not found, rather than the Boolean "true."


Similarly, filtering using these functions can be misleading. Setting a filter Findstring([Field1], "search_term")=-1 doesn't return records that contain your search term from the True output (contrary to the Boolean -1=True); rather, the True output will comprise the records that do not meet your search criteria.


Conversely, setting a filter Contains([Field1], "search_term")=-1 means the returned records are indeed true; this will return the records that do contain that search term.
 

Note: The CONTAINS function is case insensitive while the FINDSTRING function is case sensitive. You can make Contains case sensitive with the (optional) third parameter set to 0.

 

For example: Contains([Field1], 'search_term') is not case sensitive, while the following expression is: Contains([Field1], 'search_term', 0)

 

The EndsWith/ StartsWith functions are also Boolean. Filtering on EndsWith([Field1], "search_term")=-1 returns the records that end with your search term and are therefore "True."

 

More examples can be found on the Functions Help pages. Happy Alteryx-ing!

Comments
Ringo
6 - Meteoroid

This is the most ridiculous thing. Why does Alteryx handle -1 as true when using the FindString function, if -1 actually means False when using the FindString function? This inconsistency is extremely frustrating, i.e. it forces one to consider that Alteryx may not work logically. That hurts the trust that one puts into Alteryx.

 

I suggest to fix this function OR create another function which does logically work correct.

CristonS
Alteryx Alumni (Retired)

hi @Ringo thanks for your feedback; I'm sure our product management team would appreciate hearing this.  Please post to our Ideas forum for visibility.

Ringo
6 - Meteoroid
I tried posting it as a new idea several times now, but I keep getting this message:

[cid:image004.jpg@01D54DBB.B427FBC0]


Kind regards, met vriendelijke groeten, s pozdravem,

Ringo Harms
Global Reporting Manager
Procurement Shared Services
[cid:image002.jpg@01D24F0B.46DF0060]
TrevorS
Alteryx Alumni (Retired)

Hello @Ringo 

Your images were unable to load.

Can you please try to re-upload the images so I can look into this for you?

Thanks!

 

TrevorS

Community Moderator

Ringo
6 - Meteoroid
It was just plain text! There were no images attached or anything. Just text. Anyway I copied the text to notepad, then cleared out the text and then tried to post again and again it failed. This time with some error about not being validated or something. I give up. I don't want to waste time on this. Rather disappointed though in Alteryx on this matter.