Logical Functions
Starting from April 19, 2024, Business Calculation and Holistics Expression are no longer supported for new user signups, and are only be available for legacy use cases.
Holistics Expression is now replaced by AQL Expression with enhanced functionality.
Logical functions return value based on some logical conditions.
case when
case(when: condition_expression, then: value_expression, else: value_expression)
Description
The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
Return type
Vary
Example
Given a Holistics expression as below:
case(
when: users.gender == 'm', then: 'male',
when: users.gender == 'f', then: 'female',
else: 'others'
)
The SQL output would be:
Case
When users.gender = 'm' then 'male'
When users.gender = 'f' then 'female'
Else 'others'
End
And the result would be:
| gender | case |
|---|---|
| m | male |
| f | female |
| m | male |
and()
and(condition_expression, ...)
Description
Logical AND compares between two Booleans as expression and returns true when both expressions are true.
Return type
Boolean
Example
Given a Holistics expression as below:
and(
products.id >= 2,
products.id <= 8
)
The SQL output would be:
(
(products.id >= 2.0) AND (products.id <= 8.0)
)
And the result would be:
| id | and |
|---|---|
| 1 | false |
| 2 | true |
| 8 | true |
| 9 | false |
or()
or(condition_expression, ...)
Description
Logical OR compares two Booleans as expression and returns true when one of the expressions is true.
Return type
Boolean
Example
Given a Holistics expression as below:
or(condition_expression, ...)
The SQL output would be:
or(
products.id <= 2,
products.id >= 8
)
And the result would be:
| id | or |
|---|---|
| 1 | true |
| 4 | false |
| 7 | false |
| 9 | true |
not()
not(field_expression)
Description
Logical NOT takes a single Boolean as an argument and invert it.
Return type
Boolean
Example
Given a Holistics expression as below:
not(is(products.id, null))
The SQL output would be:
NOT (products.id IS NULL)
And the result would be:
| id | not |
|---|---|
| 1 | true |
| false | |
| 3 | true |
| 4 | true |
is()
is(field_expression)
Description
Logical IS evaluates the given statement and return either true or false.
Return type
Boolean
Example
Given a Holistics expression as below:
is(products.id, null))
The SQL output would be:
(products.id IS NULL)
And the result would be:
| id | not |
|---|---|
| 1 | false |
| true | |
| 3 | false |
| 4 | false |
in()
in(field_expression, value_expression, value...)
Description
in operator takes a field expression and a list of values. Return true if that list of values contains the value of that field expression.
Return type
Boolean
Example
Given a Holistics expression as below:
in(users.name, 'bob', 'alice', 'jack')
The SQL output would be:
user.name in ('bob', 'alice', 'jack')
And the result would be:
| name | in |
|---|---|
| bob | true |
| alice | true |
| peter | false |