IFNULL
IFNULL statement allows you to predefine how the GoodData platform addresses any missing values that are returned by an expression. If a metric expression that is wrapped within an IFNULL statement ever returns a null value, the replacement number specified in the second parameter of the IFNULL function is inserted in place of the null value.
IFNULL is especially useful in cases where a sub-metric is referred to within another metric’s definition. Wrapping the sub metric in an IFNULL statement keeps the encompassing metric from becoming null due to one of its components returning a null value.
Syntax
SELECT IFNULL(…,…)
SELECT IFNULL(metric, replacement_value)
Examples
SELECT IFNULL(SUM(Amount), 0)
SELECT IFNULL(SUM(FB_Cost + TW_Cost), 0)
SELECT IFNULL(SUM(Amount) + 100, 1)
Supported replacement values include constants, but not other metric expressions.
IFNULL Best Practices
The IFNULL function is used to replace missing values in fact tables and to replace values when a computation generates a null value.
A null value is not the same thing as zero (0). A null value means one of the following:
- no entry has been made
- the value is unknown
The IFNULL function can be useful for handling or trapping values that are null, which can confuse your reporting. However, the function should not be overused, as it can add significant computing time and consume workspace computational bandwidth.
Do not apply IFNULL to all of your functions or even your basic sum, average, and count functions. Instead, use IFNULL where the presence of null values is causing confusion or ambiguity in reports you have created.
Wrapping a metric in the IFNULL statement enables operations where one of the values is null. The syntax for this statement looks like the following:
SELECT IFNULL (metric_reference, replacement_value)
The IFNULL function can be applied to facts, too.
SELECT IFNULL (fact_name, replacement_value)
Example - Data load with missing line item
An IFNULL does not mean that it is a blank or missing line item. Suppose the loaded data looks like the following:
Date | Sales Amounts |
|---|---|
08/01/2013 | $3,000 |
08/02/2013 | $5,700 |
08/03/2013 | $2,000 |
08/05/2013 | $4,400 |
Wrapping the Sales Amount metric in an IFNULL function does not make data for 08/04/2013 appear because there isn’t any data for that day.
The IFNULL function does not populate rows of data when the row does not exist. Rather, it forces a value to appear when the value is blank.
Example - IFNULL and arithmetic operations
The prime example of when to use IFNULL is when you are performing an arithmetic operation, and one of the values could potentially be null. Suppose you have different types of transactions: payments, refunds, and partial refunds. You have two metrics to measure payments ( # Payments ) and refunds ( # Refunds ):
SELECT COUNT(Transaction ID) WHERE Transaction Type = Sale
SELECT COUNT(Transaction ID) WHERE Transaction Type IN (Full Refund, Partial Refund)
Suppose your daily report calculates the difference between the number of payments received and the refunds processed. The difference metric should look like the following:
SELECT # Payments - # Refunds
However, your enterprise does not process refunds every day. If no refunds are processed for a day, the above metric generates a null value, even if there are payments.
If one of the values within an expression is null, the entire expression is null. Use IFNULL to preserve the computation.
To solve this problem, you would wrap the expressions in the IFNULL function like the following:
SELECT IFNULL(# Payments,0) - IFNULL(# Refunds,0)
If a refund was not processed for a day, the calculation can still be performed.