Using Liquid Split and Array Filters to Format Lists

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

This post covers advanced subject matter and assumes good, pre-existing knowledge of Liquid and LookML.

 

The Problem
 

In data, objects often have lists of attributes associated with them. For example, a user's profile can contain multiple cities, states, or countries, depending on how often they have changed addresses. A common challenge is organizing and presenting lists of attributes in a user-friendly and digestible way. This article addresses how to render lists into easily readable formats and how to make them more interactive. To achieve this, we will use Liquid, a templating language developed by Shopify, in conjunction with the html and link LookML parameters.
 

The Solution
 

In our e-commerce data set, we have a field called "City List Raw." This is a type: string field that outputs a list of every city where users live now or have lived in the past. In our Explore below, we have the user cities affiliated with orders made in the past week:

76586794-2879-48ff-b191-b7bfef6627ac.png

To make this information more legible to end users, we want to make these list values a bulleted, unordered list. We can do this by adding an html parameter with list tags to the city_list_raw dimension. This will add the desired formatting to the values in the list.

To break the list values into individual bullets, we will leverage the Liquid split filter to divide the string into an array with the Liquid {{value}} variable, which returns the raw value of the field returned by the database query:

dimension: city_list_bullet_list { 
  type: string
  sql: ${TABLE}.`users.user_cities` ;;
  html: {% assign words = {{value}} | split: ', ' %}
  <ul>
  {% for word in words %}
  <li>{{ word }}</li>
  {% endfor %} ;;
}
NOTE: The delimiter by which a string is split into an array can be changed to accommodate different characters. For example, if list values are separated by a space, you can input '  ' to specify the space as the delimiter, rather than the comma in the example above (', ') . You can also choose to format with a different HTML list type, such as an ordered list

The result is the following: 

f2032048-5371-4c26-ba7a-3f93b35a5b73.png

 

Adding Interactivity with Links for Array Values
 

What if we want to go a step further and allow end users to drill on a single array value in a row? We can do this by adding a  link parameter to our city_list_bullet_list dimension. For example, we can add a Google search link below, allowing users to perform a Google search for a city directly from the Explore:

dimension: city_list_bullet_list {
   type: string
   sql: ${TABLE}.`users.user_cities` ;;
    html: {% assign words = {{value}} | split: ', ' %}
         <ul>
         {% for word in words %}
         <li>{{ word }}</li>
         {% endfor %} ;;
    link: {
         label: "{% assign words = {{value}} | split: ', ' %}{%if words[0] %}City Google Search {{ words[0] }}{%endif%}"
         url: "{% assign words = {{value}} | split: ', ' %}http://www.google.com/search?q={{ words[0] }}"
    }
    link: {
         label: "{% assign words = {{value}} | split: ', ' %}{%if words[1] %}City Google Search {{ words[1] }}{%endif%}"
         url: "{% assign words = {{value}} | split: ', ' %}http://www.google.com/search?q={{ words[1] }}"
    }
     link: {
         label: "{% assign words = {{value}} | split: ', ' %}{%if words[2] %}City Google Search {{ words[2] }}{%endif%}"
         url: "{% assign words = {{value}} | split: ', ' %}http://www.google.com/search?q={{ words[2] }}"
      }
    ## Add as many links as drill links you would like available
}

Here, we use the same Liquid split filter in the link label and url parameters to divide the value (captured with the {{value}} Liquid parameter). We also use the index Liquid array filter, notated with [ ], to return the array value at the specified index location. In an Explore, the links will appear as below when an array value is clicked:
 

fe7daafc-6cfd-49b3-9f2e-c7a5dd147c25.png
The Liquid index filter begins with position [0]. For example, in the screenshot above, the Aurora link corresponds with array index [0], as that is the first value in the array.

Now end users can drill into an array value of their choosing by clicking a row value. For more custom drill examples and inspiration, check out the Custom Drilling Using HTML and Link Help Center article.

NOTE ON ARRAY LINKS: You will need to add as many link parameters as array values exist in a row, depending on how many links you would like available to the users. For example, if our list can potentially output 15 values for 1 row, we will need to add 15 link parameters if we would like our users to drill by any of the 15 values in that row. If there is no value for a given link (another row in that same Explore has only 3 values), the value label will be empty and the link will not appear.
Comments
daisy-162755601
Community Visitor

I’m getting the error 

Liquid parse exception: extraneous input '{{' (around the text "ds = {{val") Note: you should use "{% if var %}" rather than "{% if {{ var }} %}" to reference variables.

when I use this code - do you know why this might be? 

Izem
New Member

I’m getting the error 

Liquid parse exception: extraneous input '{{' (around the text "ds = {{val") Note: you should use "{% if var %}" rather than "{% if {{ var }} %}" to reference variables.

when I use this code - do you know why this might be? 

Hi, 

The solution is to remove the double curly bracket around value as below:

{% assign words = value | split: ', ' %} 

Hope this helps!

jadalars
New Member

I can’t visualize the result in a table chart, anyone knows how to? It works fine to have the list split into bullets but once I have the result in a table, only the top value is displayed

I can’t visualize the result in a table chart, anyone knows how to? It works fine to have the list split into bullets but once I have the result in a table, only the top value is displayed

The current version of Looker’s default Table vis doesn’t support the list parameter. If you change the Table vis to Legacy it should display correctly.

Version history
Last update:
‎05-11-2022 11:37 AM
Updated by: