Question

Whitespace in Looker

  • 30 November 2016
  • 2 replies
  • 2405 views

Userlevel 3

What is whitespace?


Whitespace is a character that will occupy space on a page but does not correspond to a visible mark in your data. At times, the values in our table will have whitespace without us knowing it’s there!


How does whitespace affect my data?


Let’s say we have a basic user table that looks like this:


 | User ID | Gender | Hometown |
|---------|--------|----------|
| 1 | F | Atlanta |
| 2 | M | Houston |
| 3 | M | Denver |
| 4 | F | San Fran |

It doesn’t show any whitespace in our values, but what will happen if we filter on the Explore level where Hometown = ‘Denver’? We’d expect for Looker to return the third row in our table, but instead we get No Results.



Why?

This could be due to leading or trailing whitespace in ‘Denver’ that we’re not seeing because Looker “strips” it out on the front end. Whitespace is eliminated from the front end of Looker meaning that it will be removed from the display of the data table and visualizations and from any filters applied on the front end.

Since whitespace does not come through filters the WHERE clause can be constructed in such a way that it does not match the underlying data, as we saw above.


Luckily, most SQL dialects support a TRIM() function that will remove any leading and/or trailing whitespace in our values. For this example, we’ll be using MySQL’s TRIM() function, which will remove all whitespace in our field. Note that MySQL, along with other dialects, also have LTRIM() and RTRIM() functions that will remove specifically leading or trailing whitespace.


Just to be cautious, we’ll use the TRIM() function on our hometown field to remove all whitespace.



New LookML
dimension: hometown {
type: string
sql: TRIM(${TABLE}.hometown) ;;
}



Old LookML
- dimension: hometown
type: string
sql: TRIM(${TABLE}.hometown)


Now, we’ll run the same report with the filter we previously had and get the expected results!


What if I want to keep my whitespace visible on the Explore page?


As mentioned earlier, Looker “strips” out any whitespace on the Explore page. In some cases, users might want to preserve this leading whitespace for display purposes on the Explore page.


This can be done using Looker’s html parameter which we’ll define in our dimension.



New LookML
dimension: hometown {
type: string
sql: ${TABLE}.hometown ;;
html: <div style="white-space:pre;"> {{value}} </div> ;;
}



Old LookML
- dimension: hometown
type: string
sql: ${TABLE}.hometown
html: <div style="white-space:pre;"> {{value}} </div>


The {{value}} parameter will bring through all the fields from ${TABLE}.hometown and setting white-space to pre will preserve the whitespace for the underlying field.


Now, when we explore this dimension, it will look like:



You can see that Looker now shows several spaces before “Denver”.


2 replies

Hi,

I’m trying to figure out what the exact syntax would be for setting the white-space to pre. I’ve tried various combinations of curly brackets and punctuation and can’t seem to get it right. It either prints the actual words, white-space and pre, or it tells me white-space variable not found. I can’t seem to find exactly how to set this up.

Thanks!

Never mind, not sure how I missed it (I’ve looked at this page half a dozen times, which doesn’t say much for me I know), but just saw the syntax and successfully added it to my view.

Reply