Dealing with Nulls and Zeros
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.
coalesce
coalesce(val1, val2, ...., val_n)
Description
This function returns the first non-null value in a list
Return type
Vary
Example
Given a Holistics expression as below:
coalesce(yearly_payment, quarterly_payment, monthly_payment)
The result would be:
| Name | Yearly Payment | Quarterly Payment | Monthly Payment | Payment (coalesce) |
|---|---|---|---|---|
| Alice | 70.00 | NULL | NULL | 70.00 |
| Billy | NULL | 35.00 | NULL | 35.00 |
| Conte | NULL | NULL | 6.00 | 6.00 |
nullif()
nullif(expr1, expr2)
Description
This function returns NULL if two expressions are equal, otherwise it returns the first expression.
Return type
Vary
Example
Given a Holistics expression as below:
nullif(sales_target, sales_current)
The result would be:
| Sales Person | Sales Target | Sales Current | Target to be achieved (nullif) |
|---|---|---|---|
| Andy | 10,000 | 10,000 | null |
| Billy | 23,000 | 18,000 | 23,000 |
| Cindy | 21,000 | 21,000 | null |
| Danny | 0 | 10,000 | 0 |
safe_divide
safe_divide(val1, val2)
Description
Equivalent to the division operator (X / Y), but returns NULL if an error occurs, such as a division by zero error.
Return type
Vary
Example
Given a Holistics expression as below:
safe_divide(X, Y)
The result would be:
| X | Y | safe_divide | X/Y |
|---|---|---|---|
| 10 | 5 | 2 | 2 |
| 5 | 0 | null | ERROR |
| 11 | 2 | 5.5 | 5.5 |