LinkedIn
Twitter
Knowledge Drop
Last tested: Jun 22, 2020
The Problem
Have you ever wanted to highlight a portion of text in a phrase based on some defined search criteria? Well, one customer needed the same where they needed to highlight a pattern in a list of Epoch times.
In a nut-shell, Can I highlight a pattern in a list and find the number of times it occurs in the list? YES
A Solution
By using liquid HTML and some basic programming:
- Assign liquid Variables
- Iteration (For Loop) and control flow (IF statement)
- String Filters (split, remove, remove_first, prepend, append, plus)
- Contains Operator NB:
containscan only search strings. You cannot use it to check for an object in an array of objects. - Added the
Hello Wordstring because that is how we start programming ( 🙂 ). Works for regular text matches too.
Study the code below or look at the explanations below.
CODE
view: liquid_conditional_format {
derived_table: {
sql:
SELECT 1 as id, "[120, 300, 400]" as pattern, "[120, 300, 400]" as epoch_list, 5 as count UNION ALL
SELECT 2 as id, "[120, 300, 400]" as pattern, "[100, 120, 300, 400, 120, 300, 400]" as epoch_list, 5 as count UNION ALL
SELECT 3 as id, "[100, 250, 300]" as pattern, "[100, 250, 300, 400]" as epoch_list, 5 as count UNION ALL
SELECT 4 as id, "[350, 500, 600]" as pattern, "[100, 250, 300, 400]" as epoch_list, 5 as count UNION ALL
SELECT 5 as id, "[Hello]" as pattern, "[Hello, World]" as epoch_list, 5 as count
;;
}
dimension: id {
primary_key:yes
type:number
sql:${TABLE}.id;;
}
dimension: pattern {
type: string
sql:${TABLE}.pattern;;
html: <p style="text-align:center">{{value}}</p> ;;
}
dimension: matches {
type: string
sql:${TABLE}.pattern;;
html: {% assign _count = 0 %}
{% assign __pattern = value | remove: '['| remove: ']'%}
{% assign __epoch_list = epoch_list._value | remove: '['| remove: ']' | split: ', '%}
{% assign _epochlist_countseed = epoch_list._value %}
{% for item in __epoch_list %}
{% if _epochlist_countseed contains __pattern %}
{% assign _count = _count | plus: 1 %}
{% assign _epochlist_countseed = _epochlist_countseed | replace_first: __pattern, '' %}
{% endif %}
{% endfor %}
html: <p style="text-align:center"> {{ _count }}</p> ;;
}
dimension: epoch_list {
type: string
sql:${TABLE}.epoch_list;;
html: {% assign _pattern = pattern._value | remove: '['| remove: ']' | split: ', ' | join: "-" %}
{% assign _epoch_list = value | remove: '['| remove: ']' | split: ', ' | join: "-" %}
{% if _epoch_list contains _pattern %}
<p style="background-color: lightgreen; color: black; text-align:center">{{value}} <i class="fa fa-check" style="font-size:14px;color:green"></i></p>
{% else %}
<p style="background-color: orange; color: black; text-align:center">{{value}} <i class="fa fa-times" style="font-size:14px;color:red"></i></p>
{% endif %};;
}
dimension: epoch_list_highlighted {
type: string
sql:${TABLE}.epoch_list;;
html:
{% assign stripped_pattern = pattern._value | remove: '['| remove: ']' %}
{% assign highlight = stripped_pattern | prepend: '<mark>' | append: '</mark>' %}
{% if value contains stripped_pattern %}
<p style="color: black; text-align:center">{{value | replace: stripped_pattern, highlight }}</p>
{% else %}
<p style="color: black; text-align:center">{{value}}</p>
{% endif %};;
}
measure: count {
type: sum
sql:${TABLE}.count;;
}
}
Explanation of CODE
Remove the square braces to get text only (e.g. value = [120, 300, 400] becomes __pattern = 120, 300, 400)
{% assign __pattern = value | remove: '['| remove: ']'%}
Note: Assign and using liquid variables with the contains operator works best with the if statement.
{% if _epochlist_countseed contains __pattern %}
Trying to use the full expressions with the string filters and pararentheses (xxxxx) won't return a true result based on how the contains operator and liquid is processed. Feel free to test this out and notice how the if statement seems to return false when a match actually exists.
Dimension: matches
- With each loop, an item is stripped and the count is only incremented with the
plus filterif a match is found. __epoch_listarray generated using thesplit filterand', 'was used for the Loop length in thematchesdimension for this example with a short list of items. The size of the loop can be adjusted as desired.- If we were to print out the value of
_epochlist_countseedas we go through the loop, we w
[100, 120, 300, 400, 120, 300, 400] #<----- Recall Pattern was [120, 300, 400]
[100, , 120, 300, 400]
[100, , ]
[100, , ]
[100, , ]
[100, , ]
[100, , ]
2 #<----- count is two(2) based on two matches.
# You can see why the size of the size of the loop (7 items in the seed) would need adjusting to suit your scenario or be more efficient. For large datasets, it could be an expensive loop when the match is already completed. It worked for me as a goog upper limit so I left it as is as.
Dimension: epoch_list_highlighted
- This is where the actual match is visible. It was refined from the
epoch_listdimension (The 1st attempt) epoch_listdimension was based on existing documentation for conditional formatting in Looker HTML Parameter.{% assign _pattern = pattern._value | remove: '['| remove: ']' | split: ', ' | join: "-" %}.- Later realised that splitting the pattern by
', 'and joining them again using hyphens (join: "-")was an unnecessary extra step. (i.e. pattern._value = [120, 300, 400] becomes _pattern = 120-300-400) - You can replace the
prepend: '<mark>'andappend: '</mark>'with your desired form of highlighting (*, <>, -, <b></b>, <i> ...)
Resources:
- Support of this Highlight filter would save some hassle: https://help.shopify.com/en/themes/liquid/filters/additional-filters#highlight
This content is subject to limited support.
