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.
coalesce([col1,col2,col3]) |
Output: Returns the first non-missing detected in col1, col2, or col3 in that order.
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 |
Name of the column(s) searched for the first non-missing value.
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | String (column reference) | [myColumn1, myColumn2] |
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:
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 |