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.
Solved! Go to 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‘)
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 %}
;;
}
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!
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‘)
Glad to hear that it worked for you! 🙂
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 %}
;;
}
@skasira59-16573 it looks like the second liquid if should be an elsif
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 %}
;;
}