How to add conditional format for NULL (∅) values in LOOKML

Looker Q&A Looker
1

Hi,
I’m able to apply conditional format for empty values but I could not able to apply the conditional format for NULL values. Can you please help me out on how to implement this.

Question: NULL values should be appeared in red color for Zip column?

Below script is working for empty values and not working for NULL (∅) values

My Database is : Redshift

dimension: zip {
    type: zipcode
    sql: ${TABLE}.zip ;;
    html: {% if value != "" %}
    <p style="color: black; font-size:100%; text-align:left">{{ rendered_value }}</p>
    {% else %}
    <p style="color: #FF9999; background-color: #FF9999; font-size:100%; text-align:center"> NULL</p>
    {% endif %}
    ;;
  }
  dimension: zip_null {
    type: number
    sql: case when ${zip} != null then 1 else 0 end ;;
  }

dimension: zip {
type: zipcode
sql: ${TABLE}.zip ;;
html: {% if value != “” %}

{{ rendered_value }}

{% else %}

NULL

{% endif %} ;; }

dimension: zip_null {
type: number
sql: case when ${zip} != null then 1 else 0 end ;;
}

sample snapshot for you ref.

1 Like
2

Hi,

Usually, Liquid doesn’t operate on null values for dimensions and measures. A workaround to have a background colour for the null values rendered by liquid in HTML in Looker is to use COALESCE or IFNULL on the null values to convert them to any non-null value. So, that you can use this non-value in the HTML to format it as required.

More information with an example specified here:

https://community.looker.com/technical-tips-tricks-1021/liquid-doesn-t-work-in-html-link-parameter-when-value-is-null-23319

Hope this helps!

3

Hi Krishna, Thanks for your inputs.
I was able to figure out the nulls should be appeared in red color with COALESCE ({column_name},’NA‘)

4

Glad to hear that it worked for you! :slight_smile:

5

Hi Krishna and other folks,

If you look at the snapshot on the above. There was a dimension column name “City” in this we have “_ _” (special character) values available.

I want to put the conditional format for these values (Null, _ _) using html,

I wasn’t able to put the color for underscore _ _

Below html code is not working for _ _

dimension: city {

type: string

sql: ${TABLE}.city ;;

html: {% if value != ’ ’ %}

{{ rendered_value }}

{% if value != '_ _ ’ %}

{{ rendered_value }}

{% else %}

NULL

{% endif %}

;;

}

6

@skasira59-16573 it looks like the second liquid if should be an elsif

7

Thank you all for answering my questions/doubts.
conditional format is not allowed for NULL (∅) and special characters in LOOKML. We need to convert them into text or empty.
Note: empty and null (∅) is different values
Below syntax is working fine.

 dimension: city {
    type: string
    sql:case when ${TABLE}.city = '' then ''
              when ${TABLE}.city = '__' then '' else ${TABLE}.city end  ;;
     html: {% if value != "" %}
    <p style="color: black; font-size:100%; text-align:left">{{ rendered_value }}</p>
    {% else %}
    <p style="color: #FF9999; background-color: #FF9999; font-size:100%; text-align:center"> NULL</p>
    {% endif %}
    ;;
  }

For Null (∅) symbol
we need to use coalesce
sql: coalesce(${TABLE}.state__c,’ ');;

dimension: state__c {
    type: string
    sql: coalesce(${TABLE}.state__c,' ');;
    # sql: ${TABLE}.state__c  ;;
    html: {% if value != ' ' %}
    <p style="color: black; font-size:100%; text-align:left">{{ rendered_value }}</p>
    {% else %}
    <p style="color: #FF9999; background-color: #FF9999; font-size:100%; text-align:center"> NULL</p>
    {% endif %}
    ;;
  }
1 Like