Display string message if a value is null

Looker Q&A Looker
1

Hi hi!

When a calculation result is null, what’s the best way to display a text (something like “No Data Available” or “Awaiting Data”?

2

Generally, the best way is to use a coalesce function— Something like

COALESCE(-originalcalculationhere-,"Awaiting Data") will return the first non-null value. So, if -originalcalculationhere- evaluates to something that isn’t null, it’ll return that. If it evaluates to null, then it’ll show “Awaiting Data”.

The same function works in a table calculation! You might run into some troubles with datatypes if say, you’re coalescing a number to “Awaiting Data” since that’s a string.

If you run into datatype troubles, you could try using liquid HTML to change what Looker displays on the frontend without messing with the actual values.

3

Thanks! Indeed I have the “Arguments types for coalesce must all match. Types given: (Number, String)” issue, will take next to check the liquid HTML thing

4

If anyone’s interested, here’s what I did:

First, COALESCE null-values….Then, use that view dim within an href liquid variable

 dimension: id {
    type: string
    sql: ${TABLE}.id ;;
    # We add this to fill null values, for html: href functionality.
    sql: COALESCE(${TABLE}.id, 'No Available ID') ;;
  }

  dimension: uuid {
    type: string
    sql: ${TABLE}."uuid" ;;
    # We ensure we have an HTML reference, if it only exists
    html:
    {%if view_name.id._value != "No Available ID" %}

    <a href="mylink/to/the/website{{view_name.id}}/view"
    target="_blank">
    <u>{{value}}</u>
    </a> ;;
    {% else %}

      {{value}}

    {% endif %}
    ;;