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.

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 in col1, col2, or col3 in 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

See Also for COALESCE Function:

Send Feedback

Page tree