Solved: How to add conditional format for NULL (∅) values ... - Page 2

skasira59-16573 · 04-20-2023 03:14 AM

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

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

View solution in original post

skasira59-16573

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

View solution in original post

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