Question

Display string message if a value is null

  • 1 March 2019
  • 3 replies
  • 2636 views

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


3 replies

Userlevel 7
Badge +1

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 %}
;;

 

Reply