Analytics SAQL Reference

SAQL Overview
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

coalesce()

Use the coalesce() function to get the first non-null value from a list of parameters.
1coalesce(value1 , value2 , value3 , ... )
For example, the following statements ensure that a non-null grouping value is used when doing a full outer join.
1accounts = load "em/cogroup/accounts";
2opps = load "em/cogroup/opportunities";
3c = cogroup accounts by 'Year' full, opps by 'Year';
4c = foreach c generate coalesce(accounts::'Year',opps::'Year') as 'Group';
You can also use the coalesce() function to replace nulls with a default value. For example, the following statements set the default for division by zero to a non-null value.
1q = load "dataset";
2q = group q by 'Year';
3q = foreach q generate 'Year', coalesce(sum(Amount)/sum(Quantity),0) as 'AvgPrice';