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
, orcol3
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
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 | |
---|---|
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 |