Replace Null Values with coalesce()

Example: Left Outer Cogroup with coalesce()

A left outer cogroup combines the right data stream with the left data stream. If a record on the left stream does not have a match on the right stream, the missing right value comes through as null. To replace null values with a different value, use coalesce().

For example, suppose that you have a dataset of meeting information from the Salesforce Event object, and you join it with data from the Salesforce Opportunity object. This shows amount won with the total time spent in meetings.

ops = load "Ops";
meetings = load "Meetings";
q = cogroup ops by 'Account' left, meetings  by 'Company' ;
q = foreach q generate ops.'Account' as 'Account', sum(ops.'Amount') as 'sum_Amount', sum(meetings.'MeetingDuration') as 'TimeSpent';

It looks like we had no meetings with Zen Retreats.

Let’s use coalesce() to change that null value to a zero.

ops = load "Ops";
meetings = load "Meetings";
q = cogroup ops by 'Account' left, meetings  by 'Company' ;

–-use coalesce() to replace null values with zero
q = foreach q generate ops.'Account' as 'Account', sum(ops.'Amount') as 'sum_Amount', coalesce(sum(meetings.'MeetingDuration'), 0) as 'TimeSpent';