Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,741 Topics
  • 301 Replies

1741 Topics

jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Label dates by an increment (dates since x)Knowledge Drop

Last tested: Oct 10, 2019 CASEwhen ROUND(datediff / increment, 0) <= 1 then "label 1" else concat( "label 2", cast(round(datediff / increment, 0) as string))end  This content is subject to limited support.                  

100
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Why can't I download `content` as `format`?Knowledge Drop

Last tested: Oct 11, 2019 All content in Looker has slightly different allowed download formats. All downloads are contingent on having appropriate permissions. These are distinct from 'sending' and 'scheduling' content.Dashboards:PDF CSVLooks:Text CSV / Excel JSON HTML Markdown PNGExplores:Text CSV / Excel JSON HTML Markdown PNGMerged Queries:Can only be downloaded after saving to a dashboard. Must download the entire dashboard This content is subject to limited support.                  

470
jamesnestler
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Why is the Update Dependency Button not showing up in the Main Button after changes in remote projectKnowledge Drop

Last tested: Jul 23, 2020 The ProblemAfter commits are made to a remote project, users ask why they don't get a prompt to update dependencies in the local project where the remote project was imported.A SolutionClick the button to bring in the remote project files. If this is the first remote dependency you’ve added to your project, updating dependencies will also prompt Looker to create a manifest lock file. Looker uses the manifest lock file to track the version of the remote project(s).If you specify a Git branch or a Git release tag in the ref subparameter, Looker will check for new commits whenever the Looker IDE refreshes, which happens when a Looker developer goes into Development Mode, performs a Git action in the IDE, or refreshes the browser.If there are new commits, Looker will display the Update Dependencies button in the IDE so you can bring in the latest remote project files. Once you have the latest files, you can validate your LookML to verify that all of your project’s

1650
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Online sandboxes and handy testing tools for SQL databases, HTML, Javascript, CSS, ...Knowledge Drop

Last tested: May 1, 2020 Here is a list of some online Sandboxes and handy testing tools for SQL databases, HTML, Javascript, CSS, and several other programming languages1 RexTester Sandbox online for several programming languages online https://rextester.com/l/postgresql_online_compiler 2 CodeSandbox An Online IDE for Rapid Web Development https://codesandbox.io/index2 3 DB Fiddle UK Test DB2, MariaDB, Postgresql, SQLServer, SQLite, Oracle, MySQL in browser free https://dbfiddle.uk/ 4 SQL Fiddle A tool for easy online testing and sharing of database problems and their solutions. http://sqlfiddle.com/ 5 JS Fiddle New fiddle or a Boilerplate for HTML/Javascript/CSS test online https://jsfiddle.net/ 6 JS Bin HTML/Javascript/CSS test online https://jsbin.com/?html,output 7 W3schools W3Schools is a web developers site, with tutorials and references on web development languages https://www.w3schools.com/ 8 PG Exercises PostgreSQL Exercise

790
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

"Error: Must query at least one dimension or measure" (Inline_Query API Call)Knowledge Drop

Last tested: Feb 17, 2020 Recorded Use Case:User running Looker V6.24.30 was trying to get pull some details about a specific dashboard via `run_inline_query` API call in their program but ran into errors in one of the cases. Receiving "Error: Must query at least one dimension or measure" as a response.API Call:Call Type: SDK Endpoint: run_inline_query(result_format, body)More: DocsSample Logic expected in development IDE of the client (Outside of Looker e.g. Python, Ruby, NodeJS...):# instantiate Look API clientqueryAPI = looker.QueryApi(client)body = {"model":"thelook","view":"inventory_items","fields":["category.name","inventory_items.days_in_inventory_tier","products.count"],"filters":{"category.name":"socks"},"sorts":["products.count desc 0"],"limit":"500","query_timezone":"America/Los_Angeles"}resp = queryAPI.run_inline_query("json",body) The EXPERIENCERequest Body: body = {"model": "dashboard_login_model,"view": "view_name_1","fields": ["view_name_2.user_count_red","view_name_2.

3480
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

How to set up a custom Gmail/Google mail SMTP settings (Extended Guide)Knowledge Drop

Last tested: Jul 12, 2020 The ProblemUsers often come looking to change from Looker’s email service to a custom mail service. Gmail is a common choice but users run into errors during setup because certain steps haven't been followed on the Gmail/Google account end.A broad categorisation would be Google accounts intended to be used for the custom mail service could:An Account with TWO-FACTOR AUTHENTICATION (2FA) enabled. An Account without 2FA enabled.A SolutionMethod 1: Using an App Passwordwith 2FA enabled Method 2: Allow Less Secure Apps with no 2FA enabledScreenshots have been included below to share with you as a guideMethod 1: App PasswordIf 2-step verification has been enabled on the account, you can generate an app password.Follow these steps:Browse to the Security Page, notice 2FA is enabled Or go straight to the “App passwords” page. Click “Select app” and choose the app you’re using. Click “Select device” and choose the device you’re using (In this case it would be the Looke

2810
ismail
Looker Staff
1 year ago
ismail
Looker Staff
ismailLooker Staff
published in Technical Tips & Tricks

Sorting Row-limited Table calculation based on few Pivot values (Workaround)Knowledge Drop

Last tested: Sep 15, 2020 The ProblemThere are some specific scenarios when you can’t sort a table calculation:Calculations that hit a row limit, as described here. Sorting a dimension or measure after you’ve already sorted by a table calculation, as described here. Sorting a table calculation that makes use of an offset, as described here. The issue with sorting on a table calculation when a limit is reachedWhen there is no limit preventing the Table Calculation sortA Solution that could work for some uses casesThe workaround involves the use of filtered measures to replace the pivoted dimension and associated measuresThis is the conversion to make use of filtered measures (I kept the sorting based on the dimension to show the values of the calculation are the same)The equivalent table based on a filtered measure with the desired sort available on the measure and matching the table calculation values & sorting  # mySQL Database used was used here# Adjust the sql logic to suit your

2670
ismail
Looker Staff
1 year ago
iqbal.sandhu
Looker Staff
iqbal.sandhuLooker Staff
published in Technical Tips & Tricks

Methods for case insensitive string searchesKnowledge Drop

Last tested: Mar 31, 2021 You may run into a situation where you need to search for a string inside of a table calculation or a custom field, which means that you need to use a contains() function to determine if a string is present in another field. But what if you have multiple entries in a field and they might be capitalized differently? Easy! We can use the following formula:contains(lower(${my_string_dimension}),"thing i'm searching for in lower case") This content is subject to limited support.                  

1450
iqbal.sandhu
Looker Staff
1 year ago
iqbal.sandhu
Looker Staff
iqbal.sandhuLooker Staff
published in Technical Tips & Tricks

How to add commas to a Numeric String value in a Table CalculationKnowledge Drop

Last tested: May 21, 2020 The ProblemAre you trying to turn this - "Goku Power Level (957829)" into this - "Goku Power Level (957,829)"? You've come to the right place! A SolutionThis can be accomplished in a Table Calculation by using a couple of string functions, namely the length and substring function. Let's examine the basic template we'll be following, in this case for a 6 digit number, and also assume that x = the name of the field you want to add commas to:concat(substring(${x},0,length(${x})-3), ",", substring(${x}, length(${x})-2, length(${x})))Why do we need so many functions?Let's examine the code by working from the outside in of our template:concat - From a high level, all we're doing is we're concatenating the numbers before the comma, to the ones after, i.e. concat(numbers before comma, ",", numbers after comma). substring - We're using substring to extract the numbers we want, and the definition shows the basic form. For the first substring function, we're going from t

2880
iqbal.sandhu
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Incorrect number of bytes in Base64 decoded key. 32 bytes expected but 24 bytes provided.Knowledge Drop

Last tested: Nov 5, 2018 This error means that you're running with the new encryption and is using a locally backed key. The provided key doesn't match the format that we expect, in this particular case we detected the key was base64 decoded it and the base64 decoded output wasn't the proper number of bytes, which should be 32.A recommended way to generate keys is with the following:>openssl rand 32 -base64 > key_file This content is subject to limited support.                  

230
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: null (Service: AWSKMS; Status Code: 400; Error Code: InvalidCiphertextException;Knowledge Drop

Last tested: Nov 5, 2018 This error is indicative of Looker trying and failing to start with new GCM encryption.It means that this Looker is configured to use AWS Key Management Service (KMS) but KMS is unable to decrypt the stored key with the provided Customer Master Key (CMK).Ensure that the database Looker is trying to start against is from this instance and hasn't been migrated from another host with a different CMK without doing DR backup procedure. This content is subject to limited support.                  

910
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

How to adjust system keepalivesKnowledge Drop

Last tested: Jul 4, 2018 In order to comply with our recommended settings for system TCP keepalives, you may need to adjust the values. In order to do so, run the following, you will need to have sudo permisisons.Open up the file where these settings changes live:sudo vim /etc/sysctl.confadd the line:net.ipv4.tcp_keepalive_time=200and save the file.Then in order for those changes to come into effect, run:sudo sysctl --system This content is subject to limited support.                  

40
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Incorrect format for wrapped KeyKnowledge Drop

Last tested: Nov 5, 2018 This error is indicative that you could be using a local backing key for encryption but we are unable to decrypt the saved ciphertext in the database with the provided key. Verify that you are using the key they should be in the LKR_MASTER_KEY_FILE env variable and that this database was always using this key and hasn't been moved to a new host with a different key. This content is subject to limited support.                  

660
ian
Looker Staff
1 year ago
ian
Looker Staff
ianLooker Staff
published in Technical Tips & Tricks

Looker can not start because: Invalid AWS CMK AliasKnowledge Drop

Last tested: Nov 5, 2018 This error is indicative of this Looker trying and failing to start using the new GCM encryption. This means that a self-hosted or Looker hosted instance is trying to use AWS Key Management Service (KMS) for storing the encryption keys but the alias provided in the LKR_AWS_CMK environment variable is not known to KMS as being a valid key.If Looker hosted, contact our Looker support team to ensure the alias is being sourced correctly and the key exists. If your instance is self-hosted, ensure the alias exists in KMS and that the Looker instance has permission to use it and is able to access KMS. This content is subject to limited support.                  

190
ian
Looker Staff
1 year ago
H
Looker Staff
Hugo_SelbieLooker Staff
published in Technical Tips & Tricks

How do i add a custom filter to my create_query api callKnowledge Drop

Last tested: Aug 27, 2020 Something like this by amending your filter_expression parameter with the same syntax as you would put in your custom explore filter "filter_expression": "${order_items.id} > 5 AND ${users.id} < 5",  "id": 71,"view": "order_items","fields": ["orders.id","order_items.count"],"pivots": null,"fill_fields": null,"filters": nul,"filter_expression": "${order_items.id} > 5 AND ${users.id} < 5","sorts": ["order_items.count desc"] This content is subject to limited support.                  

410
H
Looker Staff
1 year ago
gavin.wims
Looker Staff
gavin.wimsLooker Staff
published in Technical Tips & Tricks

Creating hour / minute durations in visualisations with value_format [value format]Knowledge Drop

Last tested: Nov 25, 2019 If you want to represent durations in hours, minutes and seconds in a visualisation, you can do this by representing the duration as a fraction of a day, and applying [h]:mm:ss in the 'value_format' section of the visualisation settings.This can also be done with some HTML formatting in the LookML, here is an example:dimension_group: elapsed {type: durationsql_start: ${dep_raw} ;;sql_end: ${arr_raw} ;;timeframes: [second,minute,hour]} dimension: elaped_formatted {type: numbersql: ${seconds_elapsed} ;;html: {% assign h = {{value}} | divided_by: 3600 %}{% assign m = minutes_elapsed._value | modulo: 60 %}{% assign s = seconds_elapsed._value | modulo: 60 %}{{h}}h{{m}}m{{s}}s;;} This content is subject to limited support.                  

4280
gavin.wims
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Troubleshoot Action with Google Cloud FunctionKnowledge Drop

Last tested: Jun 25, 2020 ProblemThe user is trying to set up an action that update(write-back) data on BigQuery from Looks per this doc. However, they are getting this error: The form was not accepted.Go to the trigger page of the action in cloud function - the URL should be there. If you click this what does you see when that browser pops up. It might be that your action is not accessible by looker If you see this: Error: Forbidden Your client does not have permission to get URL /Looker_action_test from this serverSOLUTION:You might need to add the cloud functions invoke permission. You can test this by going to the cloud functions page, ticking the box beside their action and then info panel on the right hand side click to add members and select all_users, then the role cloud functions > cloud functions invoker This will make it public, but it will help us test if this is the issueIn case this is the issue you will either need to keep it public or manually grant the permissions s

2710
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

"Non-base queries in merged_queries must define at least one join_fields" - LookML Dashboard errorKnowledge Drop

Last tested: Jul 31, 2020 ProblemWe did the auto-convert from a UDD dashboard that has merged results to a LookML Dashboard. The UDD runs as expected. However, the LookML version is giving the error "Non-base queries in merged_queries must define at least one join_fields".SOLUTIONIf we go to the original user-defined dashboard and click "Explore From Here" on the merged results query, we might see a "Not Merged" warning in the source query from that tile. In this case we would need to fix that warning as the queries are not having a common key (join field) specified in.We could try to have a Join field explicitly mentioned… if it is not available, we can join on the row-number(like a table calc to get the row number) This content is subject to limited support.                  

370
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Percent of change on the last two pivoted columnsKnowledge Drop

Last tested: May 1, 2020 In case we have a date column that is pivoted and want to calculate the percentage of change only on the last two columns. The main issue is that the last two columns are going to be dynamic as the users can select any time range filter (last 3, 6 months and so on).In this example, the user is pivoting into Created Month column. So to get the last column we can usepivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))And combine it with the previous column getting to the whole calculation as:pivot_index(${pivotted_measure}, max(pivot_row(pivot_column())))/ pivot_index(${pivotted_measure}, max(pivot_row(pivot_column()))-1) This content is subject to limited support.                  

1250
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Dashboard Run Count versus View Total from Content Usage exploreKnowledge Drop

Last tested: Jan 25, 2021 User might want to know what is considered for the View Total from the Popular Content tile in the Content Usage Dashboard and how it differs from the Dashboard Session generated. So, which one will be closer to get how many times a dashboard was loaded?We ran some testings and could notice that:The View Total count gets updated only when a page is accessed if you reload the page. For example, page refresh or if you go to the URL via any other means The Dashboard Run Count will consider any time the dashboard was run. For instance, if a dashboard's auto-refresh it would generate a new dashboard session and added to this run count. This content is subject to limited support.                  

2490
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

How to customise the value labels showing in a GraphKnowledge Drop

Last tested: May 25, 2020 The ProblemSometimes users want to be able to customize values being shown on a line graph. So if the date range is day over days, we want to set the values being shown to only show a value every 7 days for instance. For example, the below graph is very noisy:A Solution UI FILTERSSo, in case we want to show the value every 7 days from a specific Day, we could filter on the day of the Week (Monday, Tuesday...) we want to show the values. Note that we will need to under "Edit" leave off "Plot Null Values" OR remove the filled out dates from the Date dimension so the nulls will be removed. This suggestion will look like this:Table CalculationNow, in case we would like to display values every 7 days from today. We could use the following table calculation by applying the mod() function if(mod(diff_days(${order_items.created_date},now()), 7) = 0 , yes, no)   To explain here, we get the date difference between the date field and now(). Then the mod function will div

470
flaviagdo
Looker Staff
1 year ago
flaviagdo
Looker Staff
flaviagdoLooker Staff
published in Technical Tips & Tricks

Error: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more detailsKnowledge Drop

Last tested: Jan 26, 2021 We've seen this issue when a user is connecting to a specific table on BigQuery and it is based on a Google Sheet (made sure to share it with the Looker Service Account).SOLUTIONThe solution that worked as a charm is to check the schema detection on the connection to sheet and just set them all to string This content is subject to limited support.                  

2380
flaviagdo
Looker Staff
1 year ago
fernanda.melo
Looker Staff
fernanda.meloLooker Staff
published in Technical Tips & Tricks

Create connection API - generate Base64 encoded certificateKnowledge Drop

Last tested: Nov 11, 2019 When creating a new connection using the create_connection(body) API you need to pass the certificate body for server authentication.To create this string you can run in a terminal the following command line:cat yourjsonfile.json | base64ORcat yourp12file.p12 | base64Wrap the result in quotes and use that as the value for certificate.For BigQuery certificate (string): (Write-Only) Base64 encoded Certificate body for server authentication (when appropriate for dialect).,file_type (string): (Write-Only) Certificate keyfile type - .json or .p12,--> you don't give a filename to the API to upload, you have to put the content of the file into the API request as a Base64 encoded string value This content is subject to limited support.                  

290
fernanda.melo
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

System Activity - Which users accessed specific dashboards and clicked "Run" ?Knowledge Drop

Last tested: Oct 19, 2020 We can use the "history" explore to determine who has accessed a dashboard. We can also use a custom measure to count the number of times a dashboard was run by a particular user.In the "history" explore, select the following fields:Dashboard Title User Name Dashboard ID History Count History Approximate Web Usage in MinutesAdd these filters:Dashboard ID is equal to {insert dashboard ID} Dashboard Title is equal to {insert dashboard title}Create a custom measure for "Dashboard Run Count":Field to measure: "Dashboard Session" in the History view Measure type: Count Distinct​​​​​ This content is subject to limited support.                  

3740
evelyn.shen
Looker Staff
1 year ago
evelyn.shen
Looker Staff
evelyn.shenLooker Staff
published in Technical Tips & Tricks

Set password manually for a userKnowledge Drop

Last tested: Nov 12, 2020 Go to Admin > Users > Add Users Enter the users email address and/or username if no email address is available. Finish the rest of the setup to create this user. Go back to Admin > Users > click "Edit" next to the user that you just created Select "Send rest link" next to Password. You will see a link appear. Copy this link. Go to an *incognito* browser page and paste the password reset link. Edit/Confirm the password.If the user has already been created and we're skipping to step 3 as our first step, make sure that the user was NOT set up via Google Auth, SAML, LDAP, etc. (If so, any management of passwords will have to be done in those external providers, not in Looker. See Why is there no Password Reset/Edit button for a user? for more details.)  This content is subject to limited support.                  

2650
evelyn.shen
Looker Staff
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