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
369 Replies

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

1744 Topics

Most views

Recently active Most replies Newest first

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

SQL Error: "Syntax error at or near:"Knowledge Drops

Last tested: Feb 2021 Overview This SQL error generally means that somewhere in the query, there is invalid syntax.Some common examples:Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD) Typo in the SQL (missing comma, misspelled word, etc) Missing a sql clause (missed from, join, select, etc) An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.Error Message SQL ERROR: syntax error at or nearTroubleshooting This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.TIP: To make this process easier, change the group by clause to use p

677200

1 year ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How to check the data type of a table's columns [Examples: MYSQL, POSTGRESQL, REDSHIFT, BIGQUERY]Knowledge Drop

Last tested: Jan 21, 2019 In SQL Runner, you can use these queries:MySQL:SELECTCOLUMN_NAME, DATA_TYPEFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA = 'db_name'ANDTABLE_NAME = 'table_name'ANDCOLUMN_NAME = 'column_name PostgreSQL:pg_typeof(col_name) Redshift:SELECT "column", typeFROM PG_TABLE_DEFWHERE tablename = 'table_name'AND "column" = 'column_name'BQ Legacy and Standard don't support these types of queries. But if we try and do an incorrect cast of a column in BQ, the error message will tell us the data type.

294020

1 year ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

Last tested: Jan 20, 2021If you're seeing this error, check to see if the table that is being queried is a google sheet that's stored in Drive.If that's the case, make sure that the Service Account for BQ that Looker is using has access to the Drive. This content is subject to limited support.

172319

5 months ago

ruth.mcelroyLooker Staff

asked in Technical Tips & Tricks

User does not have bigquery.jobs.create permission in project error

If we are hitting the error “BigQuery connection is hitting this error User does not have bigquery.jobs.create permission in project `project_name`” for a connection where we have granted the user the BigQuery Data Editor and BigQuery Job User role already then we may need to check our value for the `Project Name` parameter in the connection.For bigQuery connections `Project Name` in the connection panel needs to be the `Google project ID` rather than the name per our docs here. In this example we click on the project name Actions in the BQ console which shows us the project ID is actions-279322. actions-279322 is the value we need for our looker connection in this case:

11 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.

131401

1 month ago

samLooker Staff

published in Technical Tips & Tricks

samLooker Staff

published in Technical Tips & Tricks

Window Functions in LookerKnowledge Drop

Last tested: April 2021 How do I use window functions in Looker?Window functions are a very powerful tool in SQL because they perform post-aggregation calculations. There are two main ways to implement window functions in Looker:1: Use Table CalculationsCalculating after aggregations is exactly what table calculations excel at. Many common window functions in SQL can be replaced by table calcs like row, offset, or clever combinations thereof. Do keep in mind though, table calculations can only be performed on the data within the Data Tab of the Explore page!2: Use a derived tableIf table calculations don't provide what you need, you can always write a derived table with whatever SQL you want, including window functions. This content is subject to limited support.

82146

11 months ago

samLooker Staff

published in Technical Tips & Tricks

How do you reference a yesno dimension field value?Knowledge Drop

Last tested: March 2021Looker’s “yesno” dimensions return boolean values (TRUE or FALSE) rather than a string “yes” or “no”. For this reason, when SQL is involved, you don’t need to write “field == yes”, you can simply write “field”, as this will return TRUE or FALSE by itself. Here are specific examples depending on where you’d like to reference it: SQL:You can treat it as a boolean.sql: CASE WHEN ${field} THEN ...Filtered Measure: measure: first_purchase_count { type: count filters:[is_first_purchase:"Yes"]Liquid HTML{% if value == 'Yes' %} will do it.Note: it is case sensitive.Templated filter:We put a boolean in between the condition, like:derived_table: {sql:SELECT murder FROM public.rawhere {% condition filter %} murder=10 {% endcondition %} ;; }dimension: filter {type: yesnosql: ${TABLE}.murder=10;;} cite docs for type yesno , docs for filtered measures and docs for liquid variables This content is subject to limited support.

79922

1 year ago

samLooker Staff

published in Technical Tips & Tricks

Computing the Difference Between Dates (for Non-Developers)

Looker will not be updating this content, nor guarantees that everything is up-to-date. If you have developer access, check out the documentation on using the type: duration parameter with a dimension group to easily calculate interval-based durations. The ProblemI have two dates and I would like to know how much time has elapsed between them. I might want to know this result in days, months, or years. The Solution Looker's table calculations offer a simple way to compute the difference between two dates on the fly. The diff_days family of functions compares two dates and returns the difference between them as an integer.Example 1: Difference Between Two FieldsEach of the orders in the table below has a created and returned date. We can use a table calculation with the diff_days function to compute the number of days that elapsed between purchase and return:diff_days(${orders.created_date},${order_items.return_date}) Example 2: User Account Age Each of the users listed below has a sig

79280

7 months ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

How to do Period-over-Period Analysis

Looker will not be updating this content, nor guarantees that everything is up-to-date. See the Methods for Period Over Period (PoP) Analysis in Looker Community post for more examples of period-over-period analyses in Looker.The BasicsPeriod-over-period analysis is a pattern of analysis that measures something in the present and compares it to the same measurement in a comparable period of time in the past. The goal is to adjust the slice of the past you are looking at, so the same amount of time has passed in the two periods you are comparing. How-To: Year-over-Year (YOY)You can do period-over-period analysis over any defined time period. Our example is going to use year-over-year, because this is a very common use case. This example can be adjusted for comparing other time frames, such as month-over-month.In year-over-year analysis, we're looking to compare the current year to date with last year up to the same date. But if we simply filter a query for the last two years, we are goi

73671

7 months ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Calculating Moving Averages

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. Creating a Moving Average with Table CalcsSometimes we want to display overall trends in our data more clearly, using moving (or rolling) averages. We can create flexible moving averages with table calculations in Looker, using the function offset_list(). This will return a list of values in a specified window, which we can then average.To create a 7-day moving average, we will use the following calculation:mean(offset_list(${field_being_averaged},0,7)) offset_list takes three arguments: the column from which you want to grab the offset values, how far from the current row you want to start the offset, and how long you want the off

72050

7 months ago

paolaLooker Staff

published in Technical Tips & Tricks

Can I filter data using table calculations?Knowledge Drop

Last Tested: Feb 4, 2019 Indirectly yes, example in Looker Docs here.The trick is to use "hide no's from visualization". This requires you to have your "filter condition" as a boolean table calc.Note that this isn't actually filtering in the SQL clause, and that this will only work on result sets with 5000 rows or less. There is a feature request for allowing a result set filter based on table calcs. This content is subject to limited support.

68041

7 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Uploading Spreadsheet (Excel, Google Sheets) Data into Looker

Looker will not be updating this content, nor guarantees that everything is up-to-date. Author: Mark Stephens The ProblemAlthough Looker does not connect directly to an Excel spreadsheet, there are ways to upload data via a derived table.First, there is a third-party CSV to SQL Tool with which you can easily convert a CSV file into a SQL UNION ALL query, and generate a LookML derived table file to copy and paste into the appropriate project. However, if you have security concerns, there is second, more secure, method.The secondary method utilizes the CONCATENATE function to create a series of SELECT statements to ultimately create a SQL based derived table. This method works only if you have a limited number of columns and rows, such as monthly forecasting data, and is not very scalable for large spreadsheets. This method is also static and will not automatically update if the underlying spreadsheet data changes.The following example highlights how to implement the second method descr

67780

8 months ago

adina.katzLooker Staff

published in Technical Tips & Tricks

How to hide/filter certain values from visualizationKnowledge Drop

Last tested: Sep 21, 2020 Certain visualizations, i.e. line, scatterplot, area, have the "Plot Null Values" option in the visualization section. We can leverage this along with table calculations to exclude certain values from a visualization.We can do this by creating a table calculation that uses an if() function to assign "null" to values that we would like to exclude from the visualization and then toggling on the "Plot Null Values" setting.This is useful because if we were to simply filter on the field where we'd like to exclude a certain value, we would filter out all rows where that value resides. This method preserves the values in the columns in the row.doc available here This content is subject to limited support.

66860

1 year ago

davidsLooker Staff

published in Technical Tips & Tricks

Looker Multistage Development Framework (Dev->QA->Prod)

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 7.12, you can deploy any Git commit SHA, tag, or branch to production with Advanced Deploy Mode. This helps consolidate repositories in multi-environment developer workflows, where each environment points to a different version of a codebase. It also gives greater control to one or a few developers or administrators over the changes that are deployed to production.IntroductionThis article provides a comprehensive development framework for managing the promotion of LookML code and content across multiple Looker instances and is intended to aggregate various resources into a cohesive walkthrough, provide a deeper understanding of the advantages and disadvantages of having multiple Looker instances, and present a best practice framework for a development strategy. In This ArticleWhy Have Multiple Looker Instances? Release Management Is a Development Instance Necessary? Benefits and

64824

5 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Refused to display 'https://somedomain.com' in a frame because it set 'X-Frame-Options' to 'sameorigin'Knowledge Drop

Last tested: Nov 7, 2018 Let's conquer this beast.First things first. What the heck is an origin?It's the combination of protocol, domain, and port:{protocol}://{domain}:{port} --> https://developer.mozilla.org:443 OK, Got it. What about sameorigin?It's a policy designed to prohibit the display of resources from a particular origin in the page of another, different origin. When Looker is embedded in an iframe, that iframe requests and displays data from Looker's origin, which is different than the parent page's origin. For example, if I embed a dashboard from a Looker instance in https://www.w3schools.com/tags/tryit.asp?filename=tryhtml_iframe, their origins are not the same.Where does X-Frame-Options fit in?The X-Frame-Options HTTP response header can be used to indicate whether or not a browser should be allowed to render a page in an iframe. Tying this back to sameorigin, when the X-Frame-Options header is set to sameorigin, that means the iframe won't allow its contents to be r

63231

3 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?Knowledge Drop

Last Tested: Jul 1, 2018 match(${products.brand},${products.brand})if(${partition}=offset(${partition},-1) , 1+row()-${partition} , 1 This content is subject to limited support.

62828

1 year ago

paolaLooker Staff

published in Technical Tips & Tricks

Why isn't embed working in Safari? Are there browser settings we need to set for SSO embed content?Knowledge Drop

Last tested: Jan 21, 2021 Some browsers have their nuances.Safari and firefoxSafari disables third party cookies by default if the user has not previously visited the host of the iframe, so users will have to enable third party cookies in their privacy settings by disabling the setting Prevent Cross-Site Tracking. (This also applies for Safari Mobile.) Firefox has also started blocking third party cookies by default. If admins do not want to have their users manually enable third party cookies, it may require a change to the domain name of your Looker instance (like looker.x.com instead of x.looker.com) to match that of the embed application so that Looker's cookies are no longer considered "third-party." Please contact your Looker account team to inquire about this option.ChromeIn Chrome, third party cookies are enabled by default in regular windows, so issues with browser settings impacting SSO embed are not as common as in Safari, although issues related to third party cookie settin

60980

1 year ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Advanced LookML - Cohort and Retention AnalysisKnowledge Drop

Last tested: Jun 24, 2019 What is a Cohort Analysis?Read this data tutorial here (5 minutes) and check out this introduction video to learn about the fundamentals about cohort analysis: What’s the point of Cohort Analysis?The goal of a cohort analysis is to analyse the performance of cohorts relative to each other and over time. Once we understand how different cohorts are performing, we can then take action.What exactly is a “cohort”?In a nutshell, a cohort is simply a subset of users grouped by common characteristics. In the context of BI and SaaS, a cohort usually refers to a subset of users specifically segmented by some key date (i.e. the first time they visit your website, the date they perform a specific action, the date they made their first purchase/registered, etc.). Based on this key date, we can then group users together by the week / month, or whatever timeframe is useful for the business.How do we do this?By grouping users together by some key date, we can then compare di

60340

1 year ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

How to Count Only Weekdays Between Two Dates

Looker will not be updating this content, nor guarantees that everything is up-to-date. If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time:Redshift:DATEDIFF('day', start, stop) AS daysPostgres:DATEPART('day', start - stop) AS daysMySQL:DATEDIFF(stop, start) AS daysSnowflake:DATEDIFF('day', start, stop) AS daysHowever, the functions above will count all the days between the two specified dates. What if you want to exclude weekend days (Saturday and Sunday)?One way of achieving this is to create a derived table with the necessary logic. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML.Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday; this is important, or the calculations will be one or two days off. You will also want to make sure

55112

7 months ago

samLooker Staff

published in Technical Tips & Tricks

How to pass a filter condition to a derived table AND keep it in the main queryKnowledge Drop

Last tested: March 2021 The ProblemI need a condition to appear in the WHERE clause of my derived table, but I also need it to appear in the WHERE clause of my main Looker-generated query. So my final query should have the same condition in both places, like so :WITH derived_table AS ( SELECT * FROM table WHERE <condition>)SELECT * FROM derived_tableWHERE <condition>Solution 1: Using a filter fieldIn Docs, we have a great example of using a filter field to pass a condition into a derived table.view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} ;; } filter: order_region { type: string }}However, this only passes the condition into the derived table. The condition will not appear in the main query.To have the condition appear in the main query, we take advantage of the fact that the sql parameter of a filter field is placed directly into the WHERE cla

55051

4 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

SQL Error: "Syntax error at or near:"Knowledge Drops

How to check the data type of a table's columns [Examples: MYSQL, POSTGRESQL, REDSHIFT, BIGQUERY]Knowledge Drop

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentialsKnowledge Drop

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

How do I filter out null in a filtered measure?Knowledge Drop

Advanced LookML - Liquid Use CasesKnowledge Drop

Parsing JSON data in LookerKnowledge Drop

Window Functions in LookerKnowledge Drop

How do you reference a yesno dimension field value?Knowledge Drop

Can I filter data using table calculations?Knowledge Drop

How to hide/filter certain values from visualizationKnowledge Drop

Refused to display 'https://somedomain.com' in a frame because it set 'X-Frame-Options' to 'sameorigin'Knowledge Drop

How do I do a ROW_NUMBER OVER (PARTITION BY ...) with a table calc?Knowledge Drop

Why isn't embed working in Safari? Are there browser settings we need to set for SSO embed content?Knowledge Drop

Advanced LookML - Cohort and Retention AnalysisKnowledge Drop

How to pass a filter condition to a derived table AND keep it in the main queryKnowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded