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

[skasira59-16573]

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 != "" %}
    <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 ;;
  }

sample snapshot for you ref.

[skasira59-16573 · Accepted solution]

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‘)

[krishnakavya-16]

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-w...

Hope this helps!

[skasira59-16573 · Accepted solution]

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‘)

[krishnakavya-16]

Glad to hear that it worked for you! 🙂

[skasira59-16573]

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 != ' ' %}

    <p style="color: black; font-size:100%; text-align:left">{{ rendered_value }}</p>

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

    <p style="color: red; 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 %}

    ;;

  }

[n_davis]

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

[skasira59-16573 · Accepted solution]

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