Skip to main content

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:

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 :)