---
schema_version: "1"
id: 8010f0fcb896038c14b29dabb89e7025eb8f2d081981bdcf2bfcc0ab5c559ff8
title: Replace Null Values with coalesce()
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_saql/guides/concept/bi-saql-examples-coalesce.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-examples-coalesce.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 3ab27bb71fa9905216871907d6aaeb43009d18df5e0f3667429c35efc84aa91e
products:
  - Analytics SAQL Developer Guide
  - analytics
---

# Replace Null Values with coalesce()

When you use a left outer or full outer `cogroup` To blend data, unmatched data comes through as null. Use `coalesce()` To replace null values with the value of your choice.

For information about how to use the `coalesce()` Function, see [coalesce](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/reference/bi-saql-functions-coalesce.md).

## Example: Left Outer Cogroup with coalesce()

A left outer cogroup blends 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.

```nolang
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.

![Diagram showing the meeting dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_coalesce_1.png)

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

```nolang
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';
```

![Diagram showing the meeting dataset.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_coalesce_2.png)

## Example: Right Outer Cogroup with coalesce()

A right outer cogroup blends the left data stream with the right data stream. When a record on the right stream has no matching record on the left stream, The left-side fields return null. To replace null values with a different value, Use `coalesce()`.

In this example, accounts is the primary (right) dataset. Opportunities are Blended into accounts. All accounts are preserved. Accounts with no matching Opportunities return null for Sum of Amount.

```nolang
q = load "opportunity";
q2 = load "account";
q = cogroup q by 'AccountId' right, q2 by 'AccountId';
q = foreach q generate
    q2.'AccountId' as 'AccountId',
    sum(q.'Amount') as 'Sum of Amount',
    sum(q2.'AnnualRevenue') as 'Sum of AnnualRevenue';
```

It looks like 0010900000CdEfGCCX and 0010900000EfGhIEEV have no matching opportunities.

![Table showing account IDs with sum of amount and sum of annual revenue before coalesce.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_right_join_before_coalesce.png)

Let's use `coalesce()` to change those null values to zero.

```nolang
q = load "opportunity";
q2 = load "account";
q = cogroup q by 'AccountId' right, q2 by 'AccountId';

--use coalesce() to replace null values with zero
q = foreach q generate
    q2.'AccountId' as 'AccountId',
    coalesce(sum(q.'Amount'), 0) as 'Sum of Amount',
    sum(q2.'AnnualRevenue') as 'Sum of AnnualRevenue';
```

![Table showing account IDs with sum of amount and sum of annual revenue after coalesce.](https://developer.salesforce.com/media/bi_dev_guide_saql/bi_saql_right_join_after_coalesce.png)

**note:** In a right outer cogroup, every row is guaranteed to have a value from the Right data stream. Use the right dataset's field directly for the group-by Column, `q2.'AccountId'` in this example, Without wrapping it in `coalesce()`. Only The left stream's aggregated values require `coalesce()` Because those are the fields that can be null.

## Troubleshooting Null Values in Blended Datasets

_Null Values Display Instead of Zeros After a Blend_

Left and right blends use outer join semantics. When a row in one dataset has No matching row in the other, the unmatched fields return null, not zero.

To replace null values with zero, use `coalesce()` In your SAQL query.

_The Null Measure Handling Setting Doesn't Affect SAQL Results_

The Null Measure Handling setting controls how null values are displayed in Table and chart widgets. It doesn't modify the underlying SAQL query Results. If your query returns null, that null is passed to the widget Before the display setting is applied—so calculations that depend on those Values still treat them as null.

To fix null values at the query level, use `coalesce()`.

_Null Values in Calculations Return Null, Not Zero_

Null values propagate through arithmetic. For example:

- `null + 5 = null`
- `null / 2 = null`

Wrap the affected field in `coalesce()` to Substitute zero before the calculation runs.

```nolang
coalesce(sum(q.'Amount'), 0) / sum(q2.'AnnualRevenue')
```

**See Also**

- [cogroup](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/reference/bi-saql-statement-cogroup.md)