on 03-18-2016 05:05 AM - edited on 07-27-2021 11:43 PM by APIUserOpsDM
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!
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.
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.
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