Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table
Technical Tips & Tricks

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

1,744 Topics
373 Replies

When you subscribe we will email you when there is a new topic in this category

1744 Topics

Recently active

Most replies Most views Newest first

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

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.

2461

1 day ago

samLooker Staff

published in Technical Tips & Tricks

Does Looker require access to the scratch schema to write ephemeral derived tables?Knowledge Drop

Last tested: Nov 5, 2019 For most dialects a scratch schema is not needed in order to use non-persistent derived tables.For the MySQL family (Clustrix, MemSQL, MariaDB) you do need to do some additional setup to allow ephemeral derived tables. According to docs, you can just grant the proper permissions to a schema that doesn't necessarily exist.GRANTSELECT,INDEX,INSERT,DROP,CREATE TEMPORARY TABLESON looker_tmp.*;-- Note that the looker_tmp schema does not need to actually exist,-- even though these permission grants are still needed``` This content is subject to limited support.

2091

6 days ago

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.

3501

6 days ago

paolaLooker Staff

published in Technical Tips & Tricks

Can you show / hide filters on embedded content?Knowledge Drop

Last tested: Jun 8, 2020 Yes you can! The way to do this is different depending on the type of content.For User defined dashboards, you can use the embed settings option in the cog menu. (click the cog menu on the dashboard > Edit Embed Settings.) For LookML dashboards, you can use the show_filters_bar parameter in the Dashboard LookML file code for your dashboard. For example:embed_style:background_color: "#f6f8fa"show_title: truetitle_color: "#3a4245"show_filters_bar: truetile_text_color: "#3a4245text_tile_text_color: ''show_filters_bar: false For a Look, the filters are hidden by default but can be shown using the Show Filters on Embedded Looks toggle (available as of Looker 7.4). This content is subject to limited support.

25614

8 days ago

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.

3451

12 days ago

lauren.boltzCommunity Admin

published in Technical Tips & Tricks

Why do Nulls Still Appear in My Measures After Wrapping the SQL: Parameter in Coalesce()?Knowledge Drop

Example a measure of type: average (or Sum/Count/etc) is producing Nulls in an Explore. I want to display 0 instead of Null. measure: my_measure {type: averagesql: ${orders} ;;} A gut instinct may tell us to wrap the sql: parameter in a coalesce()function:measure: my_measure {type: averagesql: COALESCE(${orders},0) ;;} However, Nulls will still display for this measure in the results table.Why?The coalesce is occurring before the aggregation. The average will return Null for values where there are no rows, so the inner coalesce never has a chance to operate.How do we show 0 instead of Null?We'll need to create a new measure of type: number (which does not perform any aggregation) to wrap the measure in a coalesce(): measure: my_measure_coalesced {type: numbersql: COALESCE(${my_measure},0) ;;} Now, the measure will display 0 in place of any Null that is returned! But a pivot is forcing the nulls... Then we won't be able to use the above approach, because the nulls are coming through to

8841

13 days ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Converting Shapefiles to TopoJSON

Looker will not be updating this content, nor guarantees that everything is up-to-date. Looker uses a format for creating map layers called TopoJSON. TopoJSON stores region data in a compact format appropriate for the web. But, what if we have region data in a shapefile instead of in TopoJSON format?You can easily create TopoJSON files from many common shapefile formats. This article demonstrates how to convert a shapefile to TopoJSON using a free online tool called Mapshaper 5. For a drawing tool that will let you create a TopoJSON file by hand, check out geojson.io 3.Note: For pre-built TopoJSON map layers of commonly used shapefiles, see the Looker Map Layers repository in GitHub. The WorkflowVisit Mapshaper 5, and drag your shape file into the window: You'll see a preview of the shape: You can use the info tool to inspect each region in the shapefile to see what properties it contains. This will let you select the correct value for property_key parameter when creating custom

26141

13 days ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How can I create a new project via the API?Knowledge Drop

Last tested: Mar 8, 2018 Use the create_project call on the projects endpoint. https://docs.looker.com/reference/api-and-integration/api-reference/v3.0/project#create_project This content is subject to limited support.

571

16 days ago

margaret.abercrombieLooker Staff

published in Technical Tips & Tricks

Selectively highlight data points in visualization using a filterKnowledge Drop

Last tested: Sep 6, 2019 The ProblemQuite frequently, a sea of values need to be displayed in a visualization to capture the whole story, such as company growth over time and how different products compare to one another. Sometimes, to answer a specific question, a particular value needs to stand out from the rest. If the value is static, we can always use the filter parameter in the LookML; however, if we want the users to be able to edit the filter to their particular question, we need to use a parameter and a duplicate field.The SolutionThe process is a variation of our doc on using Liquid ParametersCreate a parameter in the view. https://docs.looker.com/reference/field-params/parameter The parameter will function if allowed_value is not added. If the user still wants to auto-populate the parameter, but has a massive amount of allowed values, they can instead use the suggest_dimension parameter to achieve the same functionality. Warning: If you will be working in an explore where

4881

20 days ago

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.

5571

20 days ago

Pieter_DeVriesLooker Staff

published in Technical Tips & Tricks

Can I change value label alignment (vertical & horizontal) in stacked bar/column charts?Knowledge Drop

Last tested: May 18, 2020 No, that is currently not possible at the moment.If that is something you're interested in, please share your use case with Looker Product team via Pendo feedback. This content is subject to limited support.

1371

30 days ago

mukulmunjal-1646050669Looker Staff

asked in Technical Tips & Tricks

"Field Usage" System Activity explore retention

Is the retention of Field Usage Explore (<your-hostname>/explore/system__activity/field_usage) is same as History explore i.e. `90 days`?

1 month ago

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_

13293

1 month ago

samLooker Staff

published in Technical Tips & Tricks

How do I make my dashboard timezone listen to the timezone defined in my embed URL?Knowledge Drop

Last tested: Nov 2020 Enable User-Specific Timezones in the Admin panel. Add the user_timezone as one of the embed URL parameters when generating the embed URL. After the embed URL loads the dashboard, the embed user can switch to Viewer Timezone in the timezone dropdown menu at the top right. But what if i want to have the embed dashboard loaded with the user-specific timezone by default?Steps 1 and 2 stay the same. Instead of having the embed user switch the timezone in Step 3, use one of the following options.Option 1: Save the dashboard with the default timezone "Viewer Timezone". This will use the user's timezone as default for both embed users and non-embed users.Option 2: We can add &query_timezone=user_timezone as part of the dashboard URL. This will make the user's timezone selected by default even if it is not the saved default timezone for the dashboard. Note that the user_timezone is one of the embed URL parameters, but the query_timezone is not an embed URL param

4641

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

What is the `POST webhooks/projects/[project]/deploy` call I see in logs?Knowledge Drop

Last tested: May 29, 2020This is the call Looker makes when creating and pushing a new branch This content is subject to limited support.

321

1 month ago

jamesnestlerLooker Staff

published in Technical Tips & Tricks

How Looker does timezones and how to troubleshoot themKnowledge Drop

Last tested: Feb 1, 2021 Timezones, yikes!You may need to troubleshoot timezones to answer questions like:Why isn't Looker converting timezones correctly? Why is the timezone CONVERT SQL wrong? Inaccurate? Is there a timezone conversion bug? My finance team says timezone conversions are incorrect because order X is missing from our Explore. Why is Looker not doing this correctly?The goal is this article is to help you understand how Looker does timezones and how that interacts with your database.How Looker does timezonesConnections Panel, Database Timezone: This is the timezone Looker THINKS dates are stored in. Will show up as one of the parameters in the SQL Looker generates to convert_timezone in generated SQL, on most dialects. Connections Panel, Query Timezone: This is the timezone Looker will attempt to convert timestamps TO. Shows up as the other parameter in the SQL Looker generates to convert_timezone. User Setting: User Timezone; "User Specific Timezones" related. This will r

37722

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

How can I use an apostrophe in a user attribute value?Knowledge Drop

Last Tested: Jan 21, 2019Just put a \ before the apostrophe!ex. Default value: O\'Donnell This content is subject to limited support.

882

2 months ago

vishal.dharmadhikariLooker Staff

published in Technical Tips & Tricks

`Comparison method violates its general contract!` error when running a query.Knowledge Drop

Last tested: Oct 6, 2020 This is a pretty weird error that's popped up a couple times. This usually appears when we've pivoted data and there's a conflict in the inputs which causes a Java issue (Stack Overflow).One option to resolve this issue is to reduce the number of columns in the Explore. This content is subject to limited support.

3071

2 months ago

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

32741

2 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

SQL Pattern: How can I count the number of comma separated values in a string?Knowledge Drop

Last tested: Aug 16, 2017 This might not be a common request, but the method to do so is below:SELECT (LENGTH(Col2) - LENGTH(REPLACE(Col2,",","")) + 1) AS MyCol2CountFROM MyTable Basically, you replace all occurrences of , with an empty string "", then subtract its LENGTH from the LENGTH of the unadulterated string, which gives you the number of , characters. This doesn't tell you the number of comma separates values, however. If you have four comma separated values, there will be three commas. In order to get the number of comma separated values, simply add 1.An example in Impala syntax is below:Source: https://stackoverflow.com/questions/19915746/count-the-number-of-elements-in-a-comma-separated-string-in-oracle This content is subject to limited support.

133481

2 months ago

Page 1 / 70

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Sign up

Already have an account? Login

Social Login

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.

Technical Tips & Tricks

Selected articles about Looker API & SDK use cases

Selected articles about LookML

Selected articles about SQL use cases in Looker

Breaking News: Knowledge Drops are LIVE!

What is an Instance Slug?Knowledge Drop

How to kill long running queries through the APIKnowledge Drop

Does Looker require access to the scratch schema to write ephemeral derived tables?Knowledge Drop

How to use access filters in derived tablesKnowledge Drop

Can you show / hide filters on embedded content?Knowledge Drop

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

Why do Nulls Still Appear in My Measures After Wrapping the SQL: Parameter in Coalesce()?Knowledge Drop

Converting Shapefiles to TopoJSON

How can I create a new project via the API?Knowledge Drop

Selectively highlight data points in visualization using a filterKnowledge Drop

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

Can I change value label alignment (vertical & horizontal) in stacked bar/column charts?Knowledge Drop

"Field Usage" System Activity explore retention

Filter a column on multiple values from another columnKnowledge Drop

How do I make my dashboard timezone listen to the timezone defined in my embed URL?Knowledge Drop

What is the `POST webhooks/projects/[project]/deploy` call I see in logs?Knowledge Drop

How Looker does timezones and how to troubleshoot themKnowledge Drop

How can I use an apostrophe in a user attribute value?Knowledge Drop

`Comparison method violates its general contract!` error when running a query.Knowledge Drop

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

SQL Pattern: How can I count the number of comma separated values in a string?Knowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded