Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

How are the csv files encoded in Looker?Knowledge Drop

Last tested: Feb 16, 2018 1. The files are encoded as utf8, 2. The default separator is a comma.3. Quotes turn into quote quote and then we quote any value that contain ", the separator or LR/CF.so a string with " in it would become "a string with "" in it" This content is subject to limited support.                  

3061
K
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Rank across pivots with table calculationsKnowledge Drop

Last tested: Sep 29, 2019 We are able to create Rank across pivots with a number of table calculations.Assuming that we have two dimensions, brand and status, and one measure, let's call it count. We created an explore, in which we have the dimension brand, we pivot on status and have the measure count and now we want to find the brands with the highest count for each status. So how do we get from this: To This: So for this we can use two nice table calculations. The first one is really obvious. We are going to use the rank function on the measure.rank(${orders.count},${orders.count}Now we got the rank in each pivot, but we don't get them to get displayed in the right order. So we need a second one to achieve this. Introducing the lookup function...lookup(row(),${calculation_1},${products.brand})This allows us to get the value of the brand dimension where the row number matches the rank that we previously created. Therefore we will get the brand that is ranked 1 in each pivot into the

14191
sergio_pestanaWarren
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How does an empty view_label (view_label: "") or label appear in the new field picker?Knowledge Drop

Last tested: Jan 19, 2021 Prior to the new explore field picker (in 7.18 and previous), using an empty view_label (e.g. view_label: "") on a join or field would default to the the name (or label) of the Explore.In the new field picker (in 7.20 and forward), any fields from a join or field that has a view_label: "" appear to be missing from the field picker, as they now default to be hidden from the field picker, rather than nested under the label of the Explore name / main view name. The fields are still accessible by searching in the field picker search bar, but this isn't intuitive to users since we cannot see them.This change will remain as is, as it now aligns the behavior between a blank view_label and a blank field label.To work around this, users should update their view_label: parameters to reflect the actual label/name of the view they'd like for these fields to be nested under in the field picker. This content is subject to limited support.                   

931
S
10 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

How are Out-of-Date PDTs Managed?Knowledge Drop

Last tested: May 22, 2018 Persistent Derived Tables are automatically reaped by Looker based on the following rules:If refresh is tied to a particular time (e.g. set via 'persist for'), then the PDT will be reaped from the database 1 hour after the persistence window expires.PDTs in Dev-Mode are treated as persist-for 24 hours until they are pushed to Production.If refresh is tied to a SQL Trigger Value, the PDT will continue to be built as long as it exists in a Production model. They will be reaped 1 hour after the associated Production model is deleted.Datagroups behave based on the sql_trigger within them. This content is subject to limited support.                  

871
M
6 months ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

How do you use refinements to change a view in only one explore in a model?Knowledge Drop

Last tested: Jan 19, 2021 Use the ordering of refinements, as mentioned in this articleLogical example: Model file includes view file for view: aand hasexplore: bjoin: aview: +adimension: newfieldexplore cjoin: aOnly explore c should include the newfield dimension. This content is subject to limited support.                   

4221
Taha
5 months ago
Peggy_Beard
Peggy_BeardMember
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.                   

16621
F
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

How can I send data to an S3 bucket via the API?Knowledge Drop

Last tested: Mar 20, 2018 You can send data to an S3 bucket using the Run Scheduled Plan Once endpoint.You'll need to replace the S3 bucket region, access_key_id, secret_access_key and query_id.{ "scheduled_plan_destination":[ { "format":"csv", "apply_formatting":false, "apply_vis":false, "address":"s3://bucketname/","type":"s3","parameters":"{\"region\":\"us-east-1\",\"access_key_id\":\"XXXXXXXX\"}","secret_parameters":"{\"secret_access_key\":\"XXXXXXXX\"}"}],"name":"big data s3 upload","query_id":<number>,"run_once":true,"send_all_results":true}  This content is subject to limited support.                    

2821
J
1 year ago
molly.lippsett
Looker Staff
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.                  

361
aloginov
3 months ago
nathaniel.thompson
Moderator
nathaniel.thompsonModerator
published in Technical Tips & Tricks

Does using derived_table or sql_table_name in an extending view override the original view's derived_table or sql_table_name?Knowledge Drop

Last tested; Mar 11, 2021 Yes! Even though usually, extends will only replace parameters that have the same name definition, sql_table_name and derived_table are special, since they're mutually exclusive. They cannot both be used at once in a single view file. Therefore they follow standard extends logic as if they were the same parameter, and the losing view's table parameter is ignored.Example: When using an explore with view_a, my_fancy_table is used in the generated SQL. When using an explore with view_b, the derived table with my_favorite_table is used in the generated SQL.view: view_a {sql_table_name: my_cool_schema.my_fancy_table ;;...} view: view_b {extends: [view_a]derived_table: { sql: SELECT * FROM my_cool_schema.my_favorite_table ;;}...}  This content is subject to limited support.                   

1801
skywalker
5 months ago
Morgan
Looker Staff
MorganLooker Staff
published in Technical Tips & Tricks

Can you change the background color of a dashboard?Knowledge Drop

Last tested: Oct 19, 2020 Only for Embed dashboards.For Dashboards-Next, it is one of the settings under Dashboard Page in Custom Themes (note that this is a license feature, so it will need to be enabled on an instance).For regular dashboards, there is an "embed settings" option that lets you edit certain options for an embedded dashboard. There are LookML dashboard parameters too. The options look like this:You can't do this for dashboards in the Looker UI. This content is subject to limited support.                  

27131
Santiago Deniz
5 months ago
Department of Customer Love
Looker Staff
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.                    

721
srikar_24
2 months ago
milli.koch
Looker Staff
milli.kochLooker Staff
published in Technical Tips & Tricks

How does caching work for downloads?Knowledge Drop

Last tested: Jun 11, 2019 Results in TableWhen downloading the same number of rows as the Explore the download query will run against the Explore cache until the cached results are invalidated.All Results/CustomWhen downloading a different number of rows than are returned in the Explore the SQL query is changed, so the download will run against the database.What about subsequent downloads?This is still unclear. Most testing seems to suggest that download queries are not stored in cache, however there have been user reports of stale data being returned in downloads with the same row limit as previous exports so the answer to this may be circumstantial. This content is subject to limited support.                  

1051
Dawid
8 months ago
maxcorbin
Looker Staff
maxcorbinLooker Staff
published in Technical Tips & Tricks

How long do async query results last for?Knowledge Drop

Last tested: Feb 12, 2018 If Instant Dashboards are enabled, then async query results are kept for 30 days.Otherwise, the default time-to-live (TTL) for an async query result set is 5 minutes. This can be changed by setting the --async-results-cache-time startup flag to the number of seconds that results should stay in cache:--async-results-cache-time=3600# set result TTL to 1 hour This content is subject to limited support.                   

361
K
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why isn't my date field being timezone converted in the WHERE clause?Knowledge Drop

Last tested: Jan 23, 2018 This question comes up from time to time and the answer is that the pattern is expected at the moment. For example, both code blocks below have Looker generated SQL for a date field. The SELECT statement has a convert timezone directly on the date field, but in the WHERE statement the convert timezone is on the date from the filter (i.e. is on the day 1-21-2018)A way to think of what is going on is as follows:The user has asked, essentially, “in my timezone (LA / PST) what events happened on 2018-01-21"? In the database timezone (UTC), that means “What events happened between 2018-01-21 08:00 - 2018-01-22 08:00? This is the reasoning for the WHERE statement.And then, Lookers converts in the SELECT because we don’t want to show the UTC time, instead we want to show the user the user specific query timezone (LA / PST). This is the reasoning for the SELECT statement. MYSQLSELECT DATE(CONVERT_TZ(order_items.returned_at ,'UTC','America/Los_Angeles')) AS `order_item

2811
i-plotnikov
7 months ago
Department of Customer Love
Looker Staff
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.                   

146711
C
3 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

CSV downloads with foreign currency renders as £ in Excel ( Euros, British Pounds, etc.)Knowledge Drop

Last tested: Aug 17, 2019This is a bug in how excel handles CSV files. Try downloading the content as XSLX instead! This content is subject to limited support.                   

491
R
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Does access to system_activity make a "viewer" user into a "standard" user?Knowledge Drop

Last tested: Aug 9, 2019 ContextIn most contracts, a "viewer" user is one who can't explore.However, in order to be able to see dashboards and Looks built off of the System Activity model, these users must be given the see_system_activity permission. Once they have this permission, they now have the "explore" tab and the ability to explore the System Activity models.Does this mean that the "viewer" is no longer a viewer, and should instead be billed as a "standard" user?AnswerEven though the 'explore' tab shows up, the user is still considered a viewer and not a standard user.​ This content is subject to limited support.                   

1121
D
4 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

If the cache is full, then what gets dropped first?Knowledge Drop

Last tested: May 2021 Looker's default max cache size is 2GB. This can be changed with the --on-disk-cache-size=<i> startup option, where <i> is a number of megabytes (so, default 2000).If your Looker instance detects that the on-disk cache exceeds the max, then it starts purging old cache entries until the limit is no longer exceeded. The algorithm to determine which entries to purge considers the age of a cache entry as well as the last time that cache entry was accessed.  This content is subject to limited support.                 

2791
Anton
4 months 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
molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

List of workarounds for sorting pivots by measure / sorting by column totals / dynamic top n rankingKnowledge Drop

Last tested: Jul 13, 2018 The problemSay you want to see some metrics for your top 10 customers by number of users, where the customer dimension is pivoted. Currently there is no easy way from an explore to sort a pivoted dimension based on measure values—you can only sort the pivoted dimension alphanumerically. Some features requests have popped up related to this issue like:Sort by Row / Sort pivots by value Rank top N / Pivots sortable by measures Sort or limit pivot columns by top valuesThere are a few workarounds you can use.Workaround #1: Derived table with rank window functionThis is the approach used in a couple of help centre articles:Sort by Pivots, Rank with Other Bucket, and Percentile Tail Analysis Dynamic Rankings (Filtering Dimensions by Custom Rank)This is the most robust solution, but it requires some work to set up the query for the derived table. Workaround #2: Sort row totals and transposeThis workaround can only be used with a table visualisation and can only suppo

46761
chriswragge
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Troubleshooting Private Embedding + SAMLKnowledge Drop

Last tested: Oct 7, 2020 There are a number of errors and settings to be aware of when using SAML authentication in an iframe for private embedding. You can test things out using a simple online HTML editor, using the following iframe code:<iframe src="https://instance.looker.com/embed/dashboards/[id]" height="500" width="500"></iframe> 401: You are not authenticated to view this pageIf you are not already logged into Looker, you will get this page by default when you attempt to access the embed URL through the iframe instead of getting the Authenticate button. To avoid this error, simply add allow_login_screen=true to the URL:<iframe src="https://instance.looker.com/embed/dashboards/[id]?allow_login_screen=true" height="500" width="500"></iframe>Note that even if you are using the sso_bypass_login_page license feature to skip the login page, you will still need to add allow_login_screen=true to avoid an error. You have to allow the login screen before you can b

9501
LucianaPadua
8 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

If a user schedules Looks and then is disabled will the scheduled Looks still go out?Knowledge Drop

Last tested: Dec 2020 If the schedule was created by a user that is now disabled, the schedule will fail.The error in the schedule history will tell you that the User is not enabled. Contact your Looker administrator to reassign this scheduled report to an enabled user.This content is subject to limited support.                

3431
mitchellposluns
6 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How can I stop or change the order of my PDT builds?Knowledge Drop

Last tested: Feb 2021ProblemI have several PDTs building, and I want to either: Pause a PDT from building Control the order of my PDT builds (maybe I want a certain one to always build first) ExplanationThe order of PDT builds is arbitrary except when dependencies are involved. So there isn't any UI for pausing a PDT or changing the order of PDT builds (here's a feature request to +1 if you'd like to see this feature).WorkaroundsTo pause trigger checks for an entire connection:You can use the PDT Maintenance Schedule to control when Looker sends trigger queries to that database.It's a cron string, which means you can get a full pause by setting the date to something in the future. For example, if today is the 2nd and you want to pause all trigger checks you could set the cron task to 0 * 31 12 * for "every hour on the 31st of December".To pause trigger checks for one PDT:You can comment out sql_trigger_value or persist_for for a given PDT and push it to production to stop it from bui

5611
S
8 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Example of using html video tag to embed a video in a Looker tableKnowledge Drop

Last tested:Jun 3, 2020 dimension: video {type: stringsql: 'https://www.w3schools.com/html/mov_bbb.mp4' ;;html: <video width="290" height="130" controls preload="none"> <controls> <source src="{{ value }}" type="video/mp4"> </video> ;;} This content is subject to limited support.                  

4811
anlee914
1 year ago
sam
Looker Staff
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

5491
M
3 months 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