Coalesce (PQL)

Returns the first non-null value from provided arguments list.

  • Returned Output: Variant
  • Library: PQL \ Granular \ Math
  • Version: 2023.00.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

Coalesce( <Column Variant> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The column needs to be an attribute with numerical values in the model.
  • The math operations are not running on measures (aka 'values' or 'metrics'). They are operating on atomic items in the attribute / column itself. These values can then be subsequently used to drive an aggregated metric.
  • For details on how to employ and use this function see the custom column overview.
Different Function types
  • This function is similar to the PQL common functions of the same name. However, it operates on a column directly at the grain.
  • As a granular function, it is used in the base SQL query itself.

The Coalesce function is used to return the first non-null value from a list of expressions. It's particularly useful for handling null (or missing) values in your data.

The COALESCE function evaluates the expressions you provide from left to right.

It returns the value of the first expression that is not null.

If all expressions are null, it returns null.

Example

coalesce([cust].[Email],[cust].[Phone],[cust].[Address])

You can see the effect from this example snippet in column #4-, based on the source columns in positions #1,#2 and #3: