coalesce - script and chart function

The coalesce function returns the first of the parameters that has a valid non-NULL representation. Any number of parameters can be used.

Syntax:

coalesce(expr1[ , expr2 , expr3 , ...])

Return data type: dual

Arguments
Argument	Description
expr1	The first expression to check for a valid non-NULL representation.
expr2	The second expression to check for a valid non-NULL representation.
expr3	The third expression to check for a valid non-NULL representation.

Examples: Chart expressions
Example	Result
Coalesce(ProductDescription, ProductName, ProductCode, 'no description available')	This expression will select between three different product description fields when some fields do not have values for the product. Following the order listed in the function, the first field with a non-null value is returned. If none of the fields contain a value, the result will be no description available.
Coalesce(TextBetween(FileName, '"', '"'), FileName)	This expression will trim potential enclosing quotes from the field FileName. If the FileName given is quoted, these are removed, and the enclosed, unquoted FileName is returned. If the TextBetween function doesn't find the delimiters it returns null, which the Coalesce rejects, returning instead the raw FileName.

Example - Coalesce fundamentals

Overview

A data table is loaded that contains product and price data. You want to ensure that any null values are replaced with an alternative value.

Open the Data load editor and add the load script below to a new section.

The load script contains:

A dataset which is loaded into a data table called Example.
The following fields in the data table:
- ProductID
- ProductName
- Price
A NullInterpret variable statement to ensure that any missing values are treated as NULL.

Load script

Set NullInterpret = '';
Example:
LOAD * INLINE [
ProductID, ProductName, Price
1, Laptop, 1000
2, , 800
3, Desktop, 1200
4, , 500
5, Tablet, 600
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:
- ProductID
- ProductName
Create the following calculated dimension:
- =Coalesce(ProductName, 'Unknown Product')
Create the following measure:
- =Sum(Price), to return a valid number representation.
Create a new table and add the following expression as a calculated dimension:
- =Coalesce(ProductName, 'Unknown Product')
Create the following measure:
- =Sum(Price), to return a valid number representation.

The results in the following tables show how the Coalesce function can replace the null values in the original dataset with the alternative value Unknown Product.

Results table 1
ProductID	ProductName	Coalesce(ProductName, 'Unknown Product')	Sum(Price)
Totals			4100
1	Laptop	Laptop	1000
2	-	Unknown Product	800
3	Desktop	Desktop	1200
4	-	Unknown Product	500
5	Tablet	Tablet	600

Results table 2
Coalesce(ProductName, 'Unknown Product')	Sum(Price)
Totals	5
Desktop	600
Laptop	1000
Tablet	600
Unknown Product	1300

Example - Replace null values with a prioritized alternative value

Overview

This example uses the Coalesce function to replace missing location data with an alternative value according to the specified priority logic.

Open the Data load editor and add the load script below to a new section.

The load script contains:

A dataset which is loaded into a data table called Example.
The following fields in the data table:
- LocationID
- Country
- State
- City
A NullInterpret variable statement to ensure that any missing values are treated as NULL.

Load script

Set NullInterpret = '';
Example:
LOAD * INLINE [
LocationID, Country, State, City
L001, USA, California, Los Angeles
L002, USA, ,
L003, Canada, Ontario,
L004, , New South Wales, Sydney
L005, UK, , London
L006, , , London
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:
- LocationID
- Country
- State
- City
Create the following calculated dimension:
- =Coalesce(Country, State, City), to populate any null values with alternatives.

Results table
LocationID	Country	State	City	Coalesce(Country, State, City)
L001	USA	California	Los Angeles	USA
L002	USA	-	-	USA
L003	Canada	Ontario	-	Canada
L004	-	New South Wales	Sydney	New South Wales
L005	UK	-	London	UK
L006	-	-	London	London

The results show that although there are missing values, you can use the Coalesce function to provide logic about how to populate these instances with an alternative value to complete the dataset. For example, L004 is missing a Country value. The Coalesce function returns New South Wales because State is the second field listed in the priority logic to use when the value for Country is missing.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here