Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,744 Topics
  • 406 Replies

1744 Topics

Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Splitting Strings into Rows in the Absence of Table-Generating Functions

Looker will not be updating this content, nor guarantees that everything is up-to-date. IntroductionMost SQL dialects don't support robust table-generating functions. There are a few common use cases for table-generating functions, such as creating a sequence of numbers or dates, un-nesting a JSON object or array into rows, or splitting a string on a delimiter into rows.In this article, we'll show an example for the last use case: Taking a string containing n distinct comma-separated values and splitting it into n unique rows.SetupSuppose we have a table of products containing id, name, and tags: productsid name tags 123 Scrunch Cloth Pants Set Pants, Throwback, 80s 1287 Le Suit Citrus Breeze Skirt Suit Skirts, Dresses, Business Casual 486 MaxStudio Plaid Romper Jumpsuits, Casual, Rompers Our goal is to un-nest the tags to create a table to map products to tags. We will assume that a table of numbers already exists in the database, though this can be created using t

38903
Beby khan
2 days ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to extract names from email address in table calcKnowledge Drop

Last tested: May 7, 2020 The ProblemUsers will sometimes want to get a substring of their dimension, for example, extracting first.last from first.last@gmail.comA SolutionWe can pretty easily do this with a table calc. We can either use 2 columns or combine them.Create a "position" column to get the length of the string before the value we want. For emails this will be @.Position: position(${users.email}, "@") Then, we create the substring table calc;Name: substring(${users.email},0,${position}-1) The above 2 steps will extract the names, if you would like to combine those into a single column it would look like:Name: substring(${users.email},0, position(${users.email}, "@")-1) This content is subject to limited support.                   

3319
Rahul12
2 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Filter On Current Week Of YearKnowledge Drop

Last tested: Jan 21, 2019 Use a custom filter:${week_of_year_dimension} = ceiling(diff_days(date(extract_years(now()),01,01),now())/7)Here we're getting the difference in days between January first of this year and today and dividing that by 7 to get the number of weeks. We use ceiling to round it up so we get the current week. This content is subject to limited support.                 

10141
JPL
2 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Dev Mode PDTsKnowledge Drop

Last tested: Sep 9, 2019 A PDT that has been created/modified in a user's dev mode has the following properties:It behaves as if it has persist_for: 24 hours regardless of what persistence is set. Each one will be its own table in the scratch schema (see "Why are there multiple copies of the same PDT in my database?"). Dev mode PDTs will NOT show in the Admin > PDT panel. (see below comment!) If you rebuild derived tables and run from your dev mode and the production SQL is exactly the same, then the prod version will be replaced. There is --if dev and --if prod syntax that allows you to modify the SQL in dev mode vs. production Dev mode PDTs with a sql_trigger_value will have an epoch timestamp on them in the hash (prod PDTs with a sql_trigger_value will not). Dev mode PDT builds will show up in the i__looker pdt_log explore with an Action Data of expires at: [timestamp] where [timestamp] is 24 hours after the build completes (as it would show for a PDT with persist_for: 24 hours).

6012
C
3 days ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

SSO Embed locale/ localisationKnowledge Drop

Last tested: Dec 10, 2020 You can set an embed users locale by adding it to the user_atttributes parameter of the url. Example;user_attributes { "locale" : "fr_FR" }If you are not seeing these being applied for an embed user;Make sure that you are using the correct and full local name from this doc https://docs.looker.com/admin-options/tutorials/localizing-looker . For example if you use "de" instead of "de_DE" for german this wont translate yourcontent. You can check what locale has been set for an embed user also by sudoing as them, and clicking on the account page from the top right of the instance.​​Just CTRL+F for "locale" and you will see the current setting for that user. This might be coming from the instances locale in the Admin> Localisation page, or from the SSO embed link itself.​ This content is subject to limited support.                  

1631
L
4 days ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How do I Referencing a Dimension from Another View in Drill Fields?Knowledge Drop

Last Tested: May 13, 2020 You can use the view_name.field_name syntax to reference a field from another view in drill fields.drill_fields: [field1, view2.field2]     This content is subject to limited support.                 

6841
R
5 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Can I customize the title of a dashboard tile?Knowledge Drop

Last Tested: Mar 10, 2021 At the moment, we aren't able to customize the font size, location, and color.You can share with the product team your use case by going to Pendo feedback - once you go into Pendo, search for ID 17627 or click on this link here: https://feedback.us.pendo.io/app/#/case/17627 This content is subject to limited support.                 

1012
Dineshkuswal
5 days ago
Eric_Lyons
Looker Staff
Eric_LyonsLooker Staff
published in Technical Tips & Tricks

Use Table Calculations to Get Most Recent Line Item When User Has Duplicate Rows (Non-subquery version)Knowledge Drop

Last tested: Mar 19, 2020 If you have line items in a table with duplicate ids, but only wants the most recent items or highest items many times you will try to use a SQL subquery to SELECT MAX(DATE) for the given parameter, but you can create this with table calculations in an explore.First, sort the explore by the ID. Then, apply a secondary sort to the date column (descending).Then, we can create a table calculation that checks if the value below the id is a duplicate.Then, we check the date column for duplicates.  This content is subject to limited support.                  

2452
Omparka Ray
8 days ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

Is there a limit to the number of looks/tiles I can have on a dashboard?Knowledge Drop

Last Tested: May 28, 2020No, but performance can be impaired with too many looks/tiles. We generally recommend a limit of around 25, which is documented here along with a warning in the product when you go above 25. This content is subject to limited support.                 

5761
Customer66
9 days ago
Michael
MichaelMember
published in Technical Tips & Tricks

Important: Supportability of Knowledge Drops

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified. We have spent years building up this treasure trove of insightful Looker tips, tricks, and how-to’s, but given how quickly Looker has evolved, we never expected this content to stay bleeding-edge forever.  Think of these Drops as point-in-time insights that were once important enough for us to document. That said, there’s a TON of extremely valuable and useful pieces of Looker knowledge here! We hope it inspires you to explore, learn, and engage with your fellow community members! If you have questions on these articles, please reply on the post for Community help! Plus, if you’re looking for officially supported resources, you can always visit our Help Center and review our Docs. 

33152
Md daud
9 days ago
mandy.chan
Looker Staff
mandy.chanLooker Staff
published in Technical Tips & Tricks

How to kill long running queries through the APIKnowledge Drop

Last tested: Apr 8, 2020 To kill a long running query through the API, you can first get a list of all running queries through the `all_running_queries` endpoint and write a script where if the running time is greater than threshold, then `kill_query`.Example code here: Kill Long Running Queries If you want to kill any long running queries that are not part of a scheduled report, the all_running_queries endpoint has a source field and you can exclude the source in the example script. This content is subject to limited support.                  

2983
Vikak kumar
9 days ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Can I integrate R and Looker? (LookR)Knowledge Drop

Last tested: Oct 25, 2019 We do have an open source R library called LookR for API 3.0. However, it's not supported and hasn't been updated recently (in the past 2+ years, use with caution). It calls the Looker API to get data into R.There are talks of updating LookR to fit the new API versions, but it is still in its early stages.This is another package that was put together by different people, not generated in the same way as our other currently supported SDK: https://github.com/looker-open-source/sdk-codegen and  github.com/Rich1000/lookrAt this point in time, we'd recommend using Python if it's an option. This content is subject to limited support.                  

6723
Hsau
9 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

PDT trigger value at a specific time and day of week, once a weekKnowledge Drop

Last Tested: Mar 22, 2021 This trigger changes weekly at 6 am on Monday:MSSQL: SELECT FLOOR((DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) - 60*60*(24*4 + 6))/(60*60*24*7))Postgres: SELECT FLOOR((EXTRACT(epoch from NOW()) - 60*60*(24*4 + 6))/(60*60*24*7))Redshift: SELECT FLOOR((EXTRACT(epoch from GETDATE()) - 60*60*(24*4 + 6))/(60*60*24*7))MySQL: SELECT FLOOR((UNIX_TIMESTAMP(NOW()) - 60*60*(24*4 + 6))/(60*60*24*7))Snowflake: SELECT FLOOR((EXTRACT(epoch from CURRENT_TIMESTAMP) - 60*60*(24*4 + 6))/(60*60*24*7))Just replace 6 with your desired hour, and 4 with the numbered day of the week from 0-6, starting from Thursday at 0. Thursday is 0 because the epoch starts at January 1, 1970, which is a Thursday.   This content is subject to limited support.                

9222
hgavilan
10 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How to use access filters in derived tablesKnowledge Drop

Last tested: Oct 31, 2019 QuestionHow do I use access filters inside the SQL of a derived table?An AnswerAll that access filters do is apply a user attribute value to a field as a condition. So, basically, just write a WHERE condition in the derived table, calling the user attribute via Liquid.ExampleIf you have an access filter like so: access_filter: {field: departmentuser_attribute: dept} and derived table SQL like so:sql: SELECT * FROM table ;; then you can simply add a WHERE to the derived table SQL like so:sql: SELECT * FROM table WHERE department = {{ _user_attributes['dept'] }} ;;  DisclaimerPlease note that the PDT Regenerator has no user attribute values, so it will input a blank for {{ _user_attributes['dept'] }} in the above example. For this reason, a derived table which references user attributes via Liquid should not be a PDT. This content is subject to limited support.                  

3881
A
11 days ago
A
Looker Staff
adina.katzLooker Staff
published in Technical Tips & Tricks

Filter a column on multiple values from another columnKnowledge Drop

Last tested: Sep 7, 2019 Say you want to filter a column by multiple values in a "sub column", i.e you have a column order id's and a column product id's, and you want to filter for order id's that contain product id A AND product id B.​​The issue: You can't use a custom filter because that filter is a row based filter and won't account for multiple row values of product id for one value of order id.The bad solution: You can use a PDT to filter on product id A then filter on product id B in the explore (or in a derived table with the filter for A as a sub select)The better solution: A group concatenation aggregate function! How this works: The aggregate function works like any other function and will concat values based on the grouping. Implementation: We can either useApproach 1: Create measure type listmeasure: product_id_list { type: list list_field: product_id;;} Approach 2: Create measure of type string and use appropriate sql dialect for group_concatmeasure: group_concat_product_

14843
Msuffdjv
11 days ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Why are Look/Dashboard IDs null in System Activity History explore?Knowledge Drop

Last tested: Apr 17, 2018 Probably because the query isn't tied to a Look or a dashboard, for example if the query was run from an explore page.There are some cases where look/dashboard ids won't show up even if the query is from a look or dashboard. API queries from looks and dashboards will show up with Source = API 3.0 and will not have a look or dashboard id. Scheduled job queries will show up with two entries in the history table, one with Source = Scheduled Task and one with Source = Saved Look, but neither one will show the look id.​ This content is subject to limited support.                  

881
Nick Lam
19 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Conditional / Dynamic Value Format value_formatKnowledge Drop

Last tested: Jan 21, 2019 We can't do dynamic formatting using the value_format parameter, but we can using the html parameter! Here is an example that looks at a separate "currency" dimension and puts the proper symbol in front of our measure amount. html:{% if currency._value == 'EUR' %}€{{rendered_value}}{% elsif currency._value == 'USD' %}${{rendered_value}}{% else %}{{rendered_value}}{% endif %};; This content is subject to limited support.                  

10202
Philipp Hagmann
20 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Reverse Running Totals

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. You can create running totals very easily with a table calculation that will sum the numbers going down the columns. If you switch the sorting, you will still get running totals going downward.What if you want to sort your data newest to oldest and have the running total calculated bottom to top? You can also do this with table calculations.Suppose I had a measure called "Flights Count" and wanted to sort by years in descending order, like the following table:The reverse running total is created with two table calculations: The first is just a normal running total:   The second gets the sum of "Flights Count" for the first row, th

16851
E
20 days ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How do I filter out null in a filtered measure?Knowledge Drop

Last tested: May 2020Most of these examples can be found in the filter expressions doc.Filter out NULL values for non-number dimensionsThe most common filter syntax to filter out nulls is simply "-NULL" . This works for most data types that aren't numbers, such as strings, dates, etc.measure: count { type: count filters: { field: user.status value: "-NULL" # assuming that user.status is a string }}Filter out NULL values for number dimensionsFor type number, use NOT NULL instead.measure: count { type: count filters: { field: user.age value: "NOT NULL" # assuming that user.age is a number }}Filter out the string "NULL" from a string dimensionWhat if your string dimension literally has the string "NULL" in it? (As opposed to a real null (Ø)) Then you'll use this special syntax:measure: count { type: count filters: { field: user.status value: "-'NULL'" # assuming that user.status is a string that actually has 'NULL" as a value }}This content is subject to limited support.                

138463
nbc1231
20 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?Knowledge Drop

Last Tested: Jul 1, 2018 match(${products.brand},${products.brand})if(${partition}=offset(${partition},-1) , 1+row()-${partition} , 1 This content is subject to limited support.                   

71369
Loretta
1 month ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How are files sent to SFTP and S3 buckets named?Knowledge Drop

Last tested: May 31, 2019 All file names look like scheduleName_timestamp_hash, where hash is a random 6-character identifier and the timestamp follows the pattern YYYY-MM-DDTHHMM (e.g. 2019-05-31T0933).There is a feature request ticket to be able to customize the file name of files sent via schedules: Change file name for data sent in schedules This content is subject to limited support.                   

4617
sassanuvat
1 month ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
posted in Technical Tips & Tricks

Creating a pie chart with over 50 rows using table calcs to create an "Other" bucket

It is sometimes the case that we need to create a pie chart, or any other chart that has row limits, but our raw data exceeds those limits.We can always make Lookml or SQL Database changes to create buckets, such as seen here:but that doesn’t always match our work flow.My solution is an explore-only purely table calc driven solution. There are 3 main parts.Creating a new dimension with “Others”, notice it is important to sort on your measure to make sure “others” matches the criteria:  states_with_others: if(row()<50,${events.state},"Others") Creating a hide column, that allows us to use yes,no to hide certain rows so that they stay within our requirements: hide_after_50: if(row()>50,no,yes) This uses the “Hide No’s from Visualization” solution seen here: https://docs.looker.com/exploring-data/using-table-calculations#using_table_calculations_in_visualizations Integrating non-”other” dimensions with “other” dimensions: count_with_others: if(${states_with_others}="Others",sum(if($

1
V
1 month ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

What HTML tags are allowed? (HTML Sanitization)Knowledge Drop

Last tested: March 2021 This doc lists all of the allowed HTML tags that can be used in html blocks in Looker. This content is subject to limited support.                

3801
leviathan
1 month ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

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 citi

36694
Pieter_DeVries
Looker Staff
1 month ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How do I hide one of my pivot columns?Knowledge Drop

Last tested: Mar 16, 2020 If you find yourself here, +1 this feature request: https://feedback.us.pendo.io/app/#/case/17612We're here, and we want to hide the "other" column:We can accomplish this with pivot_index or pivot_where.You'll need to create a table calculation for each column you want to show, then hide the original measure.pivot_index(${your_measure_here},column_to_show)This ends up looking like this:And in the visualization looks like this: This content is subject to limited support.                  

21625
Raanan.Yossef
1 month ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service