Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Knowledge base
Community ResourcesUsing the Community3 Account Info & Registration4 Community Feedback1
Community
WelcomeGetting started142 News & announcements125 Who's hiring494 Release notes (archive)63 Lesson Comments15 Challenge10
Looker developmentLookML1830 SQL319
Exploring & curating dataExplores558 Dashboards & looks1563
Application developmentEmbedding Looker / Powered by Looker155 Open source projects54 Looker API376
AdministrationUser access & management70 General Looker administration452
Data science & analyticsGot a question?83 Share your best practices16
The Kitchen TableTechnical Tips & Tricks1739 Common Account Questions14 Blog Archives347 DCL Mic Drops5 Video Tutorials 🎥4
コミュニティフォーラム (Japanese)ニュースと告知50 ヘルプとサポート14 コラム69

Ask question / Create topic
Login

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2109 Topics

Most views

Recently active Most replies Newest first

Department of Customer LoveModerator

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.

7451

5 months ago

izzyGood Looker

published in DCL Mic Drops

VincentLooker Staff

published in Technical Tips & Tricks

Can I use `hidden` on a join?Knowledge Drop

Last Tested: Mar 5, 2021 No, you can use hidden only at the explore and field level (documentation).BUT, there are workarounds. Method 1: use fields: [] instead on the join (documentation). This will effectively do the same thing as hidden would because it tells Looker to not bring any fields into the explore. Caveat: If any current content uses fields from this join, the content will break because the fields are no longer in the explore.For example:join: foo {fields: []} Method 2 (better way): use an empty view_label parameter in the join (documentation).For example:explore: orders {join: users {type: left_outersql_on: ${orders.user_id} = ${users.id} ;;relationship: many_to_oneview_label: ""}} Will show up as this in the explore: This content is subject to limited support.

7290

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

How can I put line breaks between concatenated values in a single value visualization?Knowledge Drop

Last tested: Aug 15, 2018 It is possible to add a line break to a visualization using the html parameter. The whitespace characters \r and \n are usually rendered as line breaks.For example:dimension: break { type: string html: <div style="white-space:pre">{{ value }}</div> ;; sql: concat(${field1}, "\r\n" , ${field2}) ;; } This content is subject to limited support.

7260

1 year ago

samLooker Staff

published in Technical Tips & Tricks

What's the longest allowed filter? Is there a limit on characters or values?Knowledge Drop

Last tested: March 2021 In the Looker product/code, there are no maximums on filter values or length. Theoretically, you can enter as many as you want. However, Looker uses Puma, which limits the url characters to 10240 (source: Puma Code).In the browser, a long URL can break the browser. Stackoverflow cites ~2,000 characters as the lowest limit for a browser (IE/Edge). All other browsers support URLs with > 8000 characters. As long as the URL is hashed (like in an explore), this is not a problem, but dashboard filters are simply added to the URL at the time of this writing. These problems can be solved by putting the values into a yesno dimension and filtering on that. An example:dimension: id_matches_value_list { type: yesno sql: ${id} IN (value1, value2, ... );; }In the database, there is usually a limit on query length, which is relevant since the filters usually go into the WHERE clause. A common limit is 2^16 characters (65336). There's no known workaround for this. This conte

7190

1 year ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

Is there a limit to the number of API requests you can send at one time?Knowledge Drop

Last tested: Oct 7, 2019 We only rate limit endpoints whose functionality could be abused (deliberately or accidentally) to send spam or to execute a denial of service attack on a third party. For example, the scheduled_plan_run_once endpoint is rate limited to 10 calls per second. If your app runs into this limit, you'll get an HTTP status of 429 in the response. All you need to do to avoid this is to have your script take a nap between calls to the scheduled_plan_run_once API. A sleep() or delay() of half a second (500 milliseconds) between calls would be ample.There are some peculiarities with rate limiting in a clustered environment. The rate limit trap may not fire exactly at the prescribed limit, but be a little more lax in a cluster than on a single instance. The rate limit doesn't need to be exact, it's only there to prevent malicious abuse of the system.Additionally, every node has a hard coded active thread limit of 200, and it is possible to overwhelm an instance or even a

7170

1 year ago

Peggy_BeardLooker Staff

published in Technical Tips & Tricks

Do conditional format fields need to be numeric?Knowledge Drop

Last tested: Apr 5, 2019 Yes! For table visualization conditional formatting, the fields must be numeric.If you have strings or another data type, consider using HTML in your LookML as in this Community article, or go +1 this Feature Request. This content is subject to limited support.

7150

1 year ago

ryan.bouquetLooker Staff

published in Technical Tips & Tricks

What Does "Net::SFTP::StatusException (4, "failure")" Error Mean?Knowledge Drop

Last tested: Apr 20, 2020 This is a generic error thrown by an SFTP server, it is very similar to the "RenderNoRender" error in that it's a catch-all rather than a specific error.There are a few different things that can throw this, this article goes over some common causes to check: https://winscp.net/eng/docs/sftp_codes#code_4Special considerations for Files.com SFTP ServersFor whatever reason, this is files.com's "access denied" and "path does not exist" error as well. So if they are using files.com, first check the path is correct and that the user in question has write permission to the destination folder. Note that with files.com, the path is relative to the default directory set for the user, so be sure to check that as well. This content is subject to limited support.

7090

11 months ago

Department of Customer LoveModerator

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.

7050

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Escaping quotation marks in stringsKnowledge Drop

Last tested: Jan 21, 2019 In the suggestions parameter and filter fields you can escape double quotes (") with a backslash.Example:"14\"/16\" Frame" -> 14"/16" Frame This content is subject to limited support.

6970

1 year ago

rebeccaLooker Staff

published in Technical Tips & Tricks

Troubleshooting a 401 "Unauthorized" error for API callsKnowledge Drop

Last tested: Oct 1, 2019 A 401 HTTP response indicates a problem with the authentication credentials used to make the API call. This is pretty broad, but here are some things you can check:That either the Client ID and Secret are correct, or the token is correct (the Client ID and Secret are used to get the token, but once you have the token, you don't need the ID and Secret) Whether the user making the call is an Admin, or has the required permissions to make the call That the URL doesn't end in a slash (for example, api/3.0/lookml_models works, but api/3.0/lookml_models/ does not) In Postman, if you make a call using http without the port number, you'll get a 401 error. You'll need to use https AND include the port number to avoid errors. For example: https://your_company.looker.com:19999/api/3.0/login If you are seeing a 401 on the API docs prior to logging in or making calls, it may be that you have "Require API login to see API docs" selected. You should still be able to log in f

6890

11 months ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Dynamic Rankings (Filtering Dimensions by Custom Rank)

Looker will not be updating this content, nor guarantees that everything is up-to-date. This article is about dynamically filtering dimensions based on an aggregation. You can use the examples from this article to filter a dimension by its relative ranking over any metric. The most common use case is filtering pivoted data by revenue (or other aggregation), rather than an alphanumeric string, in order to see only the top N pivoted values.Dynamic Rankings in LookerFor example, if we want to look at the number of users in each country, but want to filter the countries by the top 10 in revenue, we might think to build a is_top_10 dimension like this in our Orders view:dimension: is_top_10 { type: yesno sql: exists( select * from ( select country from orders group by country order by sum(revenue) desc limit 10 ) top_10 where ${country} = top_10.country ) ;;}This approach will work; however, it's not best practice for two reason

6880

1 month ago

samLooker Staff

published in Technical Tips & Tricks

How do I render a local image in Looker?Knowledge Drop

Last tested: March 2021 Looker expects a public URL for your image. So make sure that wherever you store it is publicly accessible. Then, you can call it in LookML like so:view: view_name { dimension: field_name { html: <img src=”http://www.acme.com/product_images/my_image.jpg” /> ;; }}This help center article goes more in depth on the modeling.It's not possible to use a local image on your computer, or on the Looker server. It is also not possible to use non-public URLs, as Looker will have no way to authenticate. This content is subject to limited support.

6825

2 months ago

ryland.wolffLooker Staff

published in Technical Tips & Tricks

What is going on when I see "nesting of 101 is too deep"?Knowledge Drop

Last tested: Jan 14, 2020 When we are creating a custom filter with more than 50 boolean evaluations we will see the error nesting of 101 is too deep. For example, we will generate this error if we have a custom filter like this:${orders.id} = 1 OR${orders.id} = 2 OR${orders.id} = 3 OR…${orders.id} = 50 OR${orders.id} = 51 With more than 50 boolean evaluations, we get too deep. We have two options to resolve this:Solution 1: LookML yesno fieldWe can create a dimension of type: yesno that will run this evaluation that we can then use as a frontend filter. This would look something like this:dimension: one_to_fifty {type: yesnosql: ${id} = 1 OR${id} = 2 OR${id} = 3…${orders.id} = 50 OR${orders.id} = 51;;} Solution 2: Use matches_filter( ) functionIf we want to avoid making any LookML developments or need to be able to modify the filters from the front end we can compress all these individual boolean evaluations into a single evaluation using the matches_filter( ) function. The logic that

6810

1 year ago

samLooker Staff

published in Technical Tips & Tricks

Can LookML dashboards be moved to another folder?Knowledge Drop

Last Tested: March 22, 2021No. LookML dashboards can only live in the LookML dashboard folder. There is this product feedback to allow functionality to move them out of the LookML dashboard folder so that they can live in other folders.As a workaround, when viewing a LookML dashboard, we can click "Copy from LookML into a folder" (documentation) from the gear menu. This will make a UDD copy of that dashboard in the folder we choose. This content is subject to limited support.

6782

1 month ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

How does "Clear Cache and Refresh" work, what if I close the browser?Knowledge Drop

Last tested: Jan 21, 2019 Looker only replaces the existing cache key when the query is complete; it does *not* begin processing a “Clear Cache and Refresh” request by actually clearing the cache, just passes a flag to not use what’s already in the cache. When the query completes, the result is placed in the cache, but if the query is killed or fails (like if you close the browser), the old cache entry remains in the cache. This content is subject to limited support.

6731

5 months ago

margaret.abercrombieLooker Staff

published in Technical Tips & Tricks

Dynamic Reference Lines with Value LabelsKnowledge Drop

Last tested: Sep 20, 2019 The ProblemSome users want reference lines in their visualization that use metrics that are not in our standard drop-down options. They could always use a custom value, but that value is static, so if it is based on a calculation of the data, it doesn't quite suit their needs. This help center article explains how to create dynamic reference lines via table calculations, but if value labels are included, this can look a little messy. This post is how to address the "value label" problem.Note:This is an imperfect solution being that I have not yet figured out how to hide the null value on bar/column visualizations. This works best with charts where the reference value will be off the main visualization. Then you can use a dark color for the data and the reference value to mask the null value.Less ideal use case:Better use case: The WorkaroundAfter creating the table calculation for the dynamic reference line:Create a second table calculation that returns the va

6700

11 months ago

chris.seymourLooker Staff

published in Technical Tips & Tricks

Why is my API call returning a 403 Forbidden error?Knowledge Drop

Last tested: Dec 28, 2018 One possible reason is that the URL is missing the port number: https://mycompany.looker.com/api/3.0/login instead of https://mycompany.looker.com:19999/api/3.0/login . Leaving the port unspecified will use the default port of 443, which isn't used for API calls. Adding :19999 to the URL should resolve the issue.This can also happen when using an out of date version of the Ruby gem. Make sure to keep those gems updated. You can check at https://rubygems.org/gems/looker-sdkThis can also happen when you don't include the /api/<version number>/ portion of the URL. If they try to hit https://mycompany.looker.com:19999/login, they'll see a 403 response. This content is subject to limited support.

6680

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Advanced LookML - Intro Aggregate AwarenessKnowledge Drop

Last tested: Jun 24, 2019 This is a useful method if you have summary tables defined in your ETL process. Otherwise, take a look at our Aggregate Awareness documentation to see if our native option works for you!Aggregate awareness is a BI term that describes the ability to use aggregate (summary) tables in a database. These summary tables contain pre-calculated data, generally rolled up by week, month, quarter, and year along with key metrics. This is particularly useful in datasets where there is a high level of granularity, stored at a transaction level. Furthermore, by leveraging liquid variables, these tables can then be referenced dynamically from a single Looker Explore.Let’s look at a common example. Suppose you wanted to get a distinct count of users per year from your event table; consider the two queries below:Query A - Default Looker query:Query B - Leverage a summary table as a CTE:What we’re actually looking at is the query plan of each query via the EXPLAIN command. This

6640

1 year ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

My LookML Validator is slow!Knowledge Drop

Last tested: Jun 25, 2020 TL;DR:The bigger the project is, the longer it takes to validate. Quick wins:Be strategic about your "includes" Always including every other view may be easy for development but being strategic by using naming conventions to only include the necessary files will increase performance. Comment or delete unused explores (and joins, fields, etc) Consider splitting your project into multiple projectsCheck out this article for LookML best practices. Long answer:LookML project size (total number of lines) and unnecessary includes both affect the performance of the LookML validator.Validation is a two-step process; 1) the first pass of validation happens by loading every single file in the project, even the ones not referenced anywhere, into memory and does a full translation of the source code one model at a time looking for "compile" errors. 2) the second pass of validation starts at each model and then parses only the files referenced by that model. And then it bu

6622

1 year ago

Peggy_BeardLooker Staff

published in Technical Tips & Tricks

How to display a filter valueKnowledge Drop

Last tested: Jun 17, 2020 The RequestWe want to display what value(s) a user chose in a filter field, either a field of type filter, or an explore field that is being filtered.A SolutionUse liquid in the html with _filters !The liquid for the _filters['view_name.field_name'] can't be used in sql but it can be used in html Here is a working example!dimension: show_my_dimension_filter {html: {% if _filters['category'] IS NOT "NULL" %} {{_filters['category']}}{% else %} no filter value {% endif %};;sql: 'this does nothing' ;;}To link the dashboard filter, link it to the field category This content is subject to limited support.

6611

3 months ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

There was a problem running the queryKnowledge Drop

Last Tested: Oct 8, 2019This is a super generic error so you'll need to do more digging. However, these questions should get you started in determining why this is happening.if you haven't already done so, confirm where you are seeing this error. We've had it reported from Explore pages and Merged Results, but other cases could exist. Are there any console errors in the developer panel in the browser? Does the query fail with this message every time? If you change the query does the error still show up? If you open this same query in Sql Runner does it return results? Find the query that failed with this message in the queries panel and click "details", what does it say there? This content is subject to limited support.

6580

1 year ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

How do I get the last n characters of a string in table calculations?Knowledge Drop

Last tested: Sep 19, 2017 You can take a substring from the end of a string by using the length function to calculate the starting point. Let n be the number of characters at the end you want to get:substring(${field_name}, length(${field_name}) - (n-1), n) If I want to get the last 4 digits of the license key for example:substring(${license_key}, length(${license_key}) - 3, 4) This works regardless of the length of the string.Chat Tip: Many use cases for table calcs require multiple functions, combined in interesting ways. Think about how you can use the pieces you have to do what you want. This content is subject to limited support.

6560

1 year ago

Page 6 / 85

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.

The Kitchen Table

Conditional / Dynamic Value Format value_formatKnowledge Drop

DCL Mic Drop: Luka FontanillaMic Drop

Why am I getting a 403 on all pages in Looker?Knowledge Drop

The life of a schedule jobKnowledge Drop

Can I use `hidden` on a join?Knowledge Drop

How can I put line breaks between concatenated values in a single value visualization?Knowledge Drop

What's the longest allowed filter? Is there a limit on characters or values?Knowledge Drop

Is there a limit to the number of API requests you can send at one time?Knowledge Drop

Do conditional format fields need to be numeric?Knowledge Drop

What Does "Net::SFTP::StatusException (4, "failure")" Error Mean?Knowledge Drop

Filter On Current Week Of YearKnowledge Drop

Escaping quotation marks in stringsKnowledge Drop

Troubleshooting a 401 "Unauthorized" error for API callsKnowledge Drop

Dynamic Rankings (Filtering Dimensions by Custom Rank)

How do I render a local image in Looker?Knowledge Drop

What is going on when I see "nesting of 101 is too deep"?Knowledge Drop

Can LookML dashboards be moved to another folder?Knowledge Drop

How does "Clear Cache and Refresh" work, what if I close the browser?Knowledge Drop

Dynamic Reference Lines with Value LabelsKnowledge Drop

Why is my API call returning a 403 Forbidden error?Knowledge Drop

Advanced LookML - Intro Aggregate AwarenessKnowledge Drop

My LookML Validator is slow!Knowledge Drop

How to display a filter valueKnowledge Drop

There was a problem running the queryKnowledge Drop

How do I get the last n characters of a string in table calculations?Knowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded