Skip to main content

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.

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

Astuce

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