COALESCE Function

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.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

coalesce([col1,col2,col3])

Output:Returns the first non-missing detected incol1,col2, orcol3in that order.

Syntax and Arguments

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.

col_ref1, col_ref2, col_ref3

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]

Examples

Tip

For additional examples, see Common Tasks.

Example - Find first time

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

Transformation:

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

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	coalesce([Heat1, Heat2, Heat3])
Parameter: New column name	'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

In this section: