Skip to content

Latest commit

 

History

History
183 lines (127 loc) · 8.22 KB

coalesce.md

File metadata and controls

183 lines (127 loc) · 8.22 KB
title
Coalesce

Coalesce

coalesce looks at the values in a list (in order), and returns the first non-null value.

This function is useful when you want to:

Syntax Example
coalesce(value1, value2, …) coalesce("null", "null", "bananas", "null" …)
Returns the first non-null value from a list of values. “bananas”

Filling in empty or null values

left_table_col right_table_col coalesce([right_table_col], 0)
1 1 1
2 null 0
3 null 0
4 4 4

You may want to fill in empty or null values if you have:

  • Sparse data.
  • null values created by a left join (the example shown above).

For a more detailed example, see Filling in data for missing report dates.

Consolidating values from different columns

Notes Comments coalesce([Notes], [Comments] "No notes or comments.")
I have a note. I have a comment. I have a note.
I have a comment. I have a comment.
I have a note. I have a note.
No notes or comments.

Creating calculations across different columns

Subtotal Discount coalesce([Subtotal], 0) - coalesce([Discount], 0)
10.00 0.15 9.85
21.00 21.00
16.00 1.60 14.40
4.00 4.00

Calculations in Metabase will return null if any of the input columns are null. This is because null values in your data represent "missing" or "unknown" information, which isn't necessarily the same as an amount of "0". That is, adding 1 + "unknown" = "unknown".

If you want to treat "unknown" values as zeroes (or some other value that means "nothing" in your data), we recommend using coalesce to wrap the columns used in your calculations.

Accepted data types

Data type Works with coalesce
String ✅
Number ✅
Timestamp ✅
Boolean ✅
JSON ❌

Limitations

Use the same data types within a single coalesce function. If you want to coalesce values that have different data types:

If you want to use coalesce with JSON or JSONB data types, you'll need to flatten the JSON objects first. For more information, look up the JSON functions that are available in your SQL dialect. You can find some common SQL reference guides here.

Related functions

This section covers functions and formulas that can be used interchangeably with the Metabase coalesce expression, with notes on how to choose the best option for your use case.

Metabase expressions

Other tools

All examples use the custom expression and sample data from the Consolidating values example:

Notes Comments coalesce([Notes], [Comments] "No notes or comments.")
I have a note. I have a comment. I have a note.
I have a comment. I have a comment.
I have a note. I have a note.
No notes or comments.

Case

The Metabase case expression

case(ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = FALSE, [Notes],
     ISBLANK([Notes]) = TRUE  AND ISBLANK([Comments]) = False, [Comments],
     ISBLANK([Notes]) = FALSE AND ISBLANK([Comments]) = TRUE,  [Notes],
     ISBLANK([Notes]) = TRUE  AND ISBLANK([Comments]) = TRUE,  "No notes or comments")

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

coalesce is much nicer to write if you don't mind taking the first value when both of your columns are non-blank. Use case if you want to define a specific output (e.g., if you want to return "I have a note and a comment" instead of "I have a note".).

SQL

In most cases (unless you're using a NoSQL database), questions created from the notebook editor are converted into SQL queries that run against your database or data warehouse.

The SQL coalesce function

SELECT
    COALESCE(notes, comments, "no notes or comments")
FROM
    sample_table;

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Spreadsheets

If your notes and comments table is in a spreadsheet where "Notes" is in column A, and "Comments" is in column B, then the formula

=IF(ISBLANK($A2),$B2,IF(ISBLANK($B2),$A2,"No notes or comments."))

is equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Alternatively, you may be used to working with a INDEX and MATCH in an array formula if you’re “coalescing” data across three or more columns in a spreadsheet.

Python

Assuming the notes and comments table is in a dataframe called df, the combination of pandas functions combine_first() and fillna()

df['custom_column'] = df['notes'].combine_first(df['comments'])\
                                 .fillna('No notes or comments.')

are equivalent to the Metabase coalesce expression:

coalesce([Notes], [Comments] "No notes or comments.")

Further reading