This article 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:

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: 

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:

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.