community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Add "BETWEEN" as an operator function

This is a pretty quick suggestion:

 

I think that there are a lot of formulas that would be easier to write and maintain if a SQL-style BETWEEN operator was available.

 

Essentially, you could turn this:

ToNumber([Postal Code]) > 1000 AND ToNumber([Postal Code]) < 2500

 

Into this:

ToNumber([Postal Code]) BETWEEN 1000 AND 2500


That way, if you later had to modify the ToNumber([Postal Code]), you only have to maintain it once.  Its both aesthetically pleasing and more maintainable!

8 Comments

Great Idea! Perhaps it could be a function like:

 

Between(ToNumber([Postal Code]),1000,2500)
Alteryx Alumni (Retired)
Status changed to: Under Review

Thanks for the request.  We will consider this as we make updates to our Formula tool functionality.

 

 

Asteroid

Status already changed to under review, so I may be beating a dead horse, but I just want to second the suggestion.

 

From the word go, I have LOVED Alteryx - still do - but I always found it odd that in tools such as e.g. formula, filter, you can't use BETWEEN.  To this day, I will sometimes type BETWEEN in there, get a parse error, and it sometimes doesn't immediately dawn on me what's wrong.

 

I think "[fieldname] between 10 and 15" is so much simpler than "[fieldname] >= 10 and [fieldname] <= 15".

Meteor

Agreed. Oracle products have a between function.

Asteroid

python allows certain comparative statements regarding the same variable to be condensed. I think this syntax makes a lot sense, since this is something we've all seen since we learned about inequalities in grade school.

(b > a and b < c) == (a < b < c)
Alteryx Partner

any updates?

Alteryx Partner

this will be awesome for dates as well...

Alteryx Certified Partner
Alteryx Certified Partner

Between is a long conversation.  I like the function.  I also like to use TILE to range values.  I like the idea of using an incoming (external) table of values to compare and get a result rather than hard-coding logic.  Sometimes the comparisons are to fields that reside on the record too.  Dynamic typing of fields is interesting to add to the function because you need to between text, numbers and dates (special text).