Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

ryan.bouquet
Looker Staff
ryan.bouquetLooker Staff
published in Technical Tips & Tricks

How do I set a filter default to filter for all results?Knowledge Drop

Last tested: Oct 28, 2019 In the default value option in the dashboard filter modal, select "is equal to" and then leave the next box blank. This will save to the dashboard as "is any value". This content is subject to limited support.                  

3020
ryan.bouquet
Looker Staff
1 year ago
ryan.bouquet
Looker Staff
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.                  

11340
ryan.bouquet
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

User doesn't have bigquery.jobs.get permissionKnowledge Drop

Last tested: Jan 7, 2020 The ProblemUser doesn't have bigquery.jobs.get permission for writing to the bigquery scratch schema.You can check if it is permission based by having them add the Admin role in big query. If this fixes their issue then try the following solution:A SolutionIn the doc for Google BigQuery it give instructions for adding for the BQ user the roles:BigQuery > BigQuery Job User BigQuery > BigQuery Data EditorIt mentions that custom roles aren't supported but if you are getting the error above try have them create a custom role to add to this user with permission bigquery.jobs.getAnother issue it could be if they have access to some PDT's but not others is that non-admin BQ users can only run jobs.get on jobs that they created themselves This content is subject to limited support.                  

2680
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

How to list all the views used in an explore in system activity?Knowledge Drop

Last tested: Jun 9, 2020 Use this link changing the domain name to your instance one.There are a few caveats though, if you have fields: [] then it won't list this view since there are no fields to list. [DOMAIN_NAME]/explore/system__activity/field_usage?fields=view_name_list,field_usage.explore&pivots=field_usage.explore&f[view_name]=-EMPTY&sorts=field_usage.explore,calculation_2+desc+0&limit=500&query_timezone=Europe%2FDublin&vis=%7B%7D&filter_config=%7B%22view_name%22%3A%5B%7B%22type%22%3A%22%21empty%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%7B%22dimension%22%3A%22view_name%22%2C%22label%22%3A%22View+name%22%2C%22expression%22%3A%22+substring%28%24%7Bfield_usage.field%7D%2C+1%2Cposition%28%24%7Bfield_usage.field%7D%2C%5C%22.%5C%22%29-1%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3Anull%2C%22_kind_hint%22%3A%22dimension%22%2C%22_type_hint%22%3A%22str

2450
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Does resetting my git connection cause PDTs to rebuild?Knowledge Drop

Last tested: Sep 20, 2019 No, this doesn't cause PDTs to rebuild. :thumbsup: This content is subject to limited support.                  

30
ruth.mcelroy
Looker Staff
1 year 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.                  

1290
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Pound Name Value Format £Knowledge Drop

Last tested: Aug 20, 2019 This format will apply a pound format to values without decimals where name_value_format is an option: [$£-en-GB]#,##0 Result:This following format will give you the decimals up to the number of zeros you add after the decimal, this example will give up to two decimal places: [$£-en-GB]#,##0.00 Result:If you want to show in millions with and "M" suffixed at the end you will need to add two commas after the 0 values you have used. Every comma will bring the decimal place up 3 points so we need two for a million, but you could use one for thousands:[$£-en-GB]#,##0.00,,"M" Result:I used excel to find this value for £. If you create a tile with a number in it> click format cells> choose the format you want from the options they have > instead of clicking okay click custom on the left and then copy the format that shows there. This content is subject to limited support.                  

6350
ruth.mcelroy
Looker Staff
1 year ago
ruth.mcelroy
Looker Staff
ruth.mcelroyLooker Staff
published in Technical Tips & Tricks

Cannot use native derived table "NDT_name" with "bind_all_filters" outside of its source explore "source_explore_name"Knowledge Drop

Last tested: Sep 22, 2020 The ProblemI have an NDT using bind_all_filters but get error:Cannot use native derived table "NDT_name" with "bind_all_filters" outside of its source explore "source_explore_name" This might be that:You have joined/explored view "NDT_name" without joining the view "source_explore_name" in too. An NDT with bind_all_filters: yes must be joined into the same Explore that is specified in the NDT’s explore_source parameter. If you want to use the NDT in a different Explore, use the bind_filters parameter instead https://docs.looker.com/reference/view-params/explore_source#bind_all_filters_example You are extending the explore with the NDT in it which we currently can't do: This content is subject to limited support.                  

2480
ruth.mcelroy
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Get the second max value with table calcKnowledge Drop

Last tested: Jun 1, 2018 We can use the approach below to exclude the max value using and if() then applying a max on the remaining valuesmax(if(${orders.count}!=max(${orders.count}), ${orders.count}, null)) (and so on for 3rd max...) For pivoted results:max(pivot_row(if(pivot_index(${orders.count},pivot_column())!=max(pivot_row(${orders.count})), ${orders.count}, null))) This content is subject to limited support.                  

860
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Quick Start card truncation rulesKnowledge Drop

Last tested: Nov 3, 2020 In Looker v7.18 Explores have added the option to display Quick Start queries, which allow the user to click on these to load pre-defined queries on the explore to get started in there analysis faster.As these Quick Cards can populate the Explore space, they are displayed in card format. These cards contain various items, title and description for example.The rules for truncating the content in these Quick Start cards is as follows:Tool tip only displays if the description needs to be truncated Tile height is determined by the tallest tile (all other tiles inherit this height)Extra rules: All tiles have a maximum and minimum pixel height. Minimum is 115px tall. Maximum is 135px Tall. All tiles have a width of 160px.If you have a title with 1 line, the description can be max 4 lines If you have a title with 2 lines, the description can be a max of 3 lines If you have a title with 3 lines, the description can be max 2 lines If you have a title with 4 lines, the d

240
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

HTML adding up and down arrow to value to cells on table vizKnowledge Drop

Last tested: Jul 30, 2018 dimension: random_value {label: "random value with html pointers"sql: rand() ;;html:{% if value > 0.5 %}{% assign indicator = "green,▲" | split: ',' %}{% elsif value < 0.5 %}{% assign indicator = "red,▼" | split: ',' %}{% else %}{% assign indicator = "black,▬"] | split: ',' %}{% endif %}<font color="{{indicator[0]}}">{% if value == 99999.12345 %} &infin{% else %}{{rendered_value}}{% endif %} {{indicator[1]}}</font> ;;} This content is subject to limited support.                  

5220
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

How do I dynamically get the max index for a pivot?Knowledge Drop

Last tested: Sep 6, 2018 pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))  This content is subject to limited support.                   

870
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

How do I show integer % value on pie chartKnowledge Drop

Last tested: Apr 11, 2019 The percentage gets calculated automatically based on the values in the Data section. Instead, if you change the label type to "Value", then the labels will be exactly what is shown in the data table. Steps:Hide the measure from the vis make a table calc that calculates the percentage${measure}/${measure:total} apply the Percent(0) formatting (or desired formatting) in the chart option change the 'Label Type' to 'Value' This content is subject to limited support.                  

5190
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

JMX monitoring when using --no-daemonise flagKnowledge Drop

Last tested: Oct 5, 2017 You will need to change all of the JMX-related startup flags by removing com.sun.akuma.jvmarg.like this:-Dcom.sun.management.jmxremote \-Dcom.sun.management.jmxremote.port=9910 \ -Dcom.sun.management.jmxremote.ssl=false \ -Dcom.sun.management.jmxremote.local.only=false \ -Dcom.sun.management.jmxremote.authenticate=true \ -Dcom.sun.management.jmxremote.access.file=${HOME}/.lookerjmx/jmxremote.access \-Dcom.sun.management.jmxremote.password.file=${HOME}/.lookerjmx/jmxremote.password \  These were a workaround to keep the daemonizer from grabbing the JMX ports, which kept the child process from starting. This content is subject to limited support.                  

460
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Why do I get the error "certificate verify failed (Faraday::SSLError)" when using the API?Knowledge Drop

Last tested: Jun 15, 2018 This error message indicates that the SSL certificate used by the Looker instance is not trusted or verifiable by the API caller. This is common when you are running a local looker instance configured with a self-signed SSL certificate.You have two choices to resolve this issue: Configure your machine to trust the self-signed certificate of your local Looker instance (instructions here), or configure your app script to ignore certificate verification.For the Looker Ruby SDK, you can do this: :connection_options => {:ssl => {:verify => false}} per this document: https://github.com/looker/looker-sdk-ruby/blob/master/readme.mdFor the Python SDK, you can do this in the .ini file: verify_ssl=False per the Configuring the SDK section of this document: https://pypi.org/project/looker-sdk/Note that skipping SSL certificate verification does not erase the value of using HTTPS for all connections to the Looker server. Certificate verification provides assurance

17260
romain.ducarrouge
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to generate LookML for all datasets for BigQuery connectionKnowledge Drop

Last tested: Apr 23, 2020 The ProblemLooker only generates one view which is specified in connection setting as dataset even we select "All tables".  A SolutionListing all datasets ( comma-separated) in schema section in create project. This content is subject to limited support.                  

370
Pieter_DeVries
Looker Staff
1 year ago
rebecca
Looker Staff
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

14600
rebecca
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

What happens to non-Looker tables that someone puts into the looker_scratch schema?Knowledge Drop

Last tested: Dec 18, 2017Looker eventually drops them if they start with lr$, lc$ or one of the legacy prefixes. This is how Looker identifies tables it manages.Other tables will presumably be ignored.  This content is subject to limited support.                  

120
quinn.morrison
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

How can I avoid a sql error when no value is selected for a parameter filter?Knowledge Drop

Last tested: May 29, 2020 Unlike templated filters, which will default to 1=1 when no filter value is selected, a parameter with no filter value selected will cause a sql error. If you need to use parameters instead of templated filters, and you want to avoid this error when no filter value is selected, we can add this to our sql:{% if view_name.parameter_name._is_filtered %} [parameter filter statement] {% else %} 1=1 {% endif %}  This content is subject to limited support.                  

620
quinn.morrison
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a User Activation Report?Knowledge Drop

Last tested: Nov 28, 2019 The ProblemTo look at three KPIs,1. Active Users (Activity within last 30 days)2. Retained Active Users3. Re-Activated Users (Activity within last 30 days, but no activity from 30 - 60 days, but activity from 90 days +)A SolutionIn order to do this, we will need some date manipulation and some windows functions, meaning we will use a derived table.Here is an idea of how to create this, using Standard SQL (Bigquery) Required data is User ID connected to an Activity Date (basically, what date they performed an activity on.) We will create a derived table like below, only needing to change the parts in [] with your data set. WITH daily_use AS (SELECT[MEMBER_ACCOUNT_ID] as user_id, cast(TIMESTAMP_TRUNC(timestamp_millis(TIMESTAMP_IN_HOUR),day) as date) as activity_dateFROM [DATABASE.TABLE]GROUP BY 1, 2)SELECTdaily_use.user_id, wd.date as date, CASE WHEN MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) < 30 THEN "yes"ELSE "no" END as within_30, CASE WHEN M

510
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a First and Last date using Custom FieldsKnowledge Drop

Last tested: Jan 8, 2020 The ProblemSay you want to see a user's first and last query dates. This could be pretty difficult because it is in system_activity so you can't edit the lookml, and you can't easily min/max dates.A SolutionWe will use a combination of Custom Dimensions, Custom Measure and Table Calcs to achieve this.Here is a picture of what that looks like.Verify that you have Custom Fields enabled. Open an explore that has the data you are looking for. Set up the explore to have your Main Dimension (in this case we are using User ID). Everything else will be custom. Create the custom dimension, this will create a number that shows the difference in days between the date we are interested in and today.diff_days(${history.created_date},now()) Create the Custom Measures. 1. Day_diff_1 (this is the last_day) 2. Day_diff_2 (this is the first day) Same as above, just change measure type from min to max. Create the table calcs that show the dates. 1. Last_dateadd_days(${day_diff_1}

5540
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

Can I use whitespace in filters?Knowledge Drop

Last tested: Mar 3, 2020 Starting in 7.4, YES!Say you have this table with a combination of leading and trailing spaces:In the past, Looker would trim all trailing and leading spaces from filter values, even if those spaces carried meaning for the customer's dataset. Now, you can include whitespaces in filter values without Looker trimming them!Here are some examples:Matches advanced works too! The important thing to remember is the ^ character is needed to escape the whitespace character(s), like:Please NOTE: With great power comes great responsibility. Since Looker no longer trims trailing and leading whitespaces, it's possible to get unexpected results if you accidentally include a whitespace in your filter value. This content is subject to limited support.                  

1360
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

String-type User Attributes and % WildcardsKnowledge Drop

Last tested: May 11, 2020 Starting in Looker 7.8 (and backport patches for 7.4 and 7.6), "string" type User Attributes will no longer support the "%" wildcard character in group or user values. Any "%" characters in a "string" type User Attribute's group or user values will be treated as regular, non wildcard characters. Note, this may impact the results of queries using such User Attributes in filters (specifically access_filters), as the "%" character will go from "matches anything" to "equals '%'". If "%" wildcard characters are needed in string-based User Attributes, please use the "string (advanced)" type as our docs recommend. This content is subject to limited support.                  

4520
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

ERROR : Internal database configuration issueKnowledge Drop

Last tested: Nov 11, 2020 Starting in 7.20, Looker now validates the dialect and version of the internal database during startup. If something other than the default in-memory Hypersql or a Mysql v5.7.x database is used as the internal database, Looker logs an error message before performing any startup migrations. There are two possible error messages:"The Looker internal database is not the default in-memory HyperSQL database or a MySQL 5.7.x database" This is thrown when you are using a supported dialect (i.e. hypersql by default, or mysql via looker-db.yml) but not a supported version (ex: mysql v8.0.1). "{dialect name} is not a valid dialect option for the Looker internal database" This is thrown when you aren't using a supported dialect in looker-db.yml (ex: aurora, mariadb, etc.). Because Looker is developed and tested around hypersql and mysql v5.7.x databases, those are the only two supported configurations. Given our lack of testing beyond hypersql and mysql v5.7.x, any dev

700
Pieter_DeVries
Looker Staff
1 year ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

Can I track slack actions in system activity?Knowledge Drop

Last tested: Jul 21, 2020 Yes! With the event attribute explore.Sample URL to share:/explore/system__activity/event_attribute?fields=event.created_time,user.name,event.name,event.category,event_attribute.name,event_attribute.value&f[event.name]=%25slack%25&sorts=event.created_time+desc&limit=500&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22event.name%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22slack%22%7D%2C%7B%7D%5D%2C%22id%22%3A2%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%5D&origin=share-expanded This content is subject to limited support.                  

270
Peggy_Beard
1 year 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