Skip to main content

Dealing with Nulls and Zeros

Legacy Feature

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:

NameYearly PaymentQuarterly PaymentMonthly PaymentPayment (coalesce)
Alice70.00NULLNULL70.00
BillyNULL35.00NULL35.00
ConteNULLNULL6.006.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 PersonSales TargetSales CurrentTarget to be achieved (nullif)
Andy10,00010,000null
Billy23,00018,00023,000
Cindy21,00021,000null
Danny010,0000

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:

XYsafe_divideX/Y
10522
50nullERROR
1125.55.5

Let us know what you think about this document :)