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