between(X;Y;Z)

Returns a boolean value indicating whether a given value is between two other values.

Syntax

between(X;Y;Z)

Input

Argument Type Description
X any simple type The value to test within the range defined by Y and Z
Y any simple type The minimum value of the range in which to check for X
Note: The order of Y and Z doesn't matter.

If Y is the name of a column, Z must also be the name of a column.

Z any simple type The maximum value of the range in which to check for X
Note: The order of Y and Z doesn't matter.

If Z is the name of a column, Y must also be the name of a column.

Note: All arguments must be of the same type. This function is most commonly used with numeric values; however, if strings are provided as arguments, the comparison is done as a binary string comparison.

Return Value

Returns an integer value of 1 if either:
  • X>=Y and X<=Z
  • X>=Z and X<=Y
Returns a 0, otherwise.

If X is N/A, the result is 0, unless either Y or Z (or both) are N/A, in which case the result is 1.

If X, Y, and Z are not either all numeric values or all string values, an error is returned.

Sample Usage

value begin end between(value;begin;end)
2 1 3 1
2 3 1 1
1.5 1 3 1
1.5 2.5 3.1 0
1.5 1.2 3.1 1
'bat' 'bar' 'baz' 1
'bag' 'bar' 'baz' 0
'foo' 'bar' 'baz' 0
1 3 0
1 1

Example

In the "Sales Item Detail" table (pub.demo.retail.item), you can find only those rows whose values in the Sales column fall between 1 and 2.25. To do this, create a computed column and apply the between(X;Y;Z) function to the sales column, where Y is 1 and Z is 2.25:
<note type="base">Applied to table: pub.demo.retail.item</note>
<willbe name="between_result" value="between(sales;1;2.25)"/>

For those values in the sales column that fall between 1 and 2.25, the result is 1. Otherwise, the result is 0.