Display string message if a value is null

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”?

0 3 7,056
3 REPLIES 3

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.

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

 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 %}
;;
Top Labels in this Space
Top Solution Authors