Amazon QuickSight – nullIf

The ‘nullIf’ function in Amazon QuickSight returns a null value if expression1 and expression2 are equal, otherwise, it returns the value of expression1.

Syntax

#Start#
nullIf(expression1, expression2)
#End#

This function takes two arguments:

• expression1 (required): the expression to check.

• expression2 (required): the expression to compare against.

Suppose you have a dataset containing customer names and their ages, but some customers do not have an age listed as NULL. You want to create a calculated field that replaces the NULL values with a default value of 30.

Assume the following dataset:

To replace the NULL values with a default value of 30, you can create a calculated field using the nullIf function:

• Select the dataset that you want to create a calculated field for.
• Click on the “Add field” button.
• Enter the following expression:

Example

#Start#

nullIf({Age}, NULL) or 30

#End#

This expression checks if the value of the Age field is NULL, and if so, replaces it with the default value of 30. If the value of the Age field is not NULL, the expression returns the value of the Age field.

• Click “Create field” to create the calculated field.

After creating this calculated field, any NULL values in the Age field will be replaced with the default value of 30. In this example, the calculated field would have the following values:

Note that the “Age or 30” column is a calculated field, and the values are not stored in the original dataset.