Dealing with Nulls and Zeros
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 | normal_division |
|---|---|---|---|
| 10 | 5 | 2 | 2 |
| 5 | 0 | null | ERROR |
| 11 | 2 | 5.5 | 5.5 |