COALESCE Function

Contents:

Expand all Collapse all

Function returns the first non-missing value found in an array of columns.

The order of the columns listed in the function determines the order in which they are searched.

If you need to perform analysis across multiple columns of heterogeneous data, see Analyze across Multiple Columns.
If you need to perform analysis across multiple homogeneous columns, see Calculate Metrics across Columns.

Basic Usage

derive type:single value: COALESCE([col1,col2,col3]) as: 'firstValue'

Output: Generates the firstValue column, which contains the first non-missing detected in col1, col2, or col3 in that order.

derive type:single value: COALESCE([col_ref1,col_ref2, col_ref3] )

A reference to a single column does not require brackets. References to multiple columns must be passed to the function as an array of column names.

Argument	Required?	Data Type	Description
col_ref1	Y	string	Name of the first column to find the first non-missing value
col_ref2	N	string	Name of the second column to find the first non-missing value
col_ref3	N	string	Name of the third column to find the first non-missing value

For more information on syntax standards, see Language Documentation Syntax Notes.

Name of the column(s) searched for the first non-missing value.

Usage Notes:

Required?	Data Type	Example Value
Yes	String (column reference)	`[myColumn1, myColumn2]`

Tip: For additional examples, see Common Tasks.

You are tracking multiple racers across multiple heats. Racers might sit out heats for various reasons.

Source:

Here's the race data.

Racer	Heat1	Heat2	Heat3
Racer X		38.22	37.61
Racer Y	41.33		38.04
Racer Z	39.27	39.04	38.85

Transform:

Use the following transform to grab the first non-missing value from the Heat columns:

derive type:single value:COALESCE([Heat1, Heat2, Heat3]) as:'firstTime'

Results:

Racer	Heat1	Heat2	Heat3	firstTime
Racer X		38.22	37.61	38.22
Racer Y	41.33		38.04	41.33
Racer Z	39.27	39.04	38.85	39.27

See Also for COALESCE Function:

Send Feedback