Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

Trifacta Dataprep


If you licensed Dataprep by Trifacta before Oct. 14, 2020, you are using the Dataprep by Trifacta Legacy product edition. On October 14, 2022, this product edition will be decommissioned by Google and will be no longer available for use. Current customers of this product edition are encouraged to transition to one of the product editions hosted by Trifacta. See Product Editions.



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


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.

ArgumentRequired?Data TypeDescription
col_ref1YstringName of the first column to find the first non-missing value
col_ref2NstringName of the second column to find the first non-missing value
col_ref3NstringName 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 TypeExample Value
YesString (column reference)[myColumn1, myColumn2]


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.


Here's the race data.

Racer X 38.2237.61
Racer Y41.33 38.04
Racer Z39.2739.0438.85


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'


Racer X 38.2237.6138.22
Racer Y41.33 38.0441.33
Racer Z39.2739.0438.8539.27

  • No labels

This page has no comments.