COALESCE Function

D toc

Excerpt
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.

D s

snippet	Basic

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

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

D s

snippet	Syntax

D code
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

D s lang notes

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

D s

snippet	usage

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

D s

snippet	Examples

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:

D code
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

D s also

label	other

Versions Compared