Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2115 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

779800

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.

308590

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.

189229

7 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:

1 year ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker

Looker will not be updating this content, nor guarantees that everything is up-to-date. Looker can implement period-over-period (PoP) analysis with a number of methods that vary in functionality and modeling difficulty. These factors need to be balanced depending on requirements and expertise. This article addresses eight methods for performing PoP analysis, outlined in the chart below. Use the chart to determine the appropriate method for your use case, and select the name of the desired PoP analysis method in the Method implementations section below: Method implementationsSelect the name of the desired PoP analysis method below to be directed to the implementation instructions. A majority of the examples use an order_items table from an e-commerce dataset. The example LookML view below is the basis of these examples:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source

150570

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

147721

3 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Advanced LookML - Liquid Use CasesKnowledge Drop

Last tested: Aug 28, 2019 Liquid is an open-source, Ruby-based template language created by Shopify It can be used in conjunction with LookML to build a more flexible, dynamic code.Liquid code is denoted by braces { } and falls into 3 different categories:TAG: {% %}used to create logicOBJECT:{{ }}, {{ value }}output pieces of dataFILTERS:modify the output of numbers, strings, objects, and variables,denoted by a pipe character |{{ value | capitalize }} = VALUEWe can pass values through multiple filters at the same time to compute complex sums.{{ product.sale_price | minus: product.price | times: 100.0 | divided_by: product.sale_price }}Check out the following external links, there is some really good content here to get deeper into all the things Liquid can do. And handy to have for reference.Cheat SheetLiquid WikiConditional LogicWhere can I use Liquid in Looker? There are several places in LookML where Liquid can be used:The html parameterThe HTML parameter enables you to specify the

140262

1 year ago

samLooker Staff

published in Technical Tips & Tricks

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

Last tested: May 2020Most of these examples can be found in the filter expressions doc.Filter out NULL values for non-number dimensionsThe most common filter syntax to filter out nulls is simply "-NULL" . This works for most data types that aren't numbers, such as strings, dates, etc.measure: count { type: count filters: { field: user.status value: "-NULL" # assuming that user.status is a string }}Filter out NULL values for number dimensionsFor type number, use NOT NULL instead.measure: count { type: count filters: { field: user.age value: "NOT NULL" # assuming that user.age is a number }}Filter out the string "NULL" from a string dimensionWhat if your string dimension literally has the string "NULL" in it? (As opposed to a real null (Ø)) Then you'll use this special syntax:measure: count { type: count filters: { field: user.status value: "-'NULL'" # assuming that user.status is a string that actually has 'NULL" as a value }}This content is subject to limited support.

139773

24 days ago

jamesnestlerLooker Staff

posted in Technical Tips & Tricks

No Matching Signature for operator >= for argument types: TYPE, OTHER TYPE

SQL Error Series #1: Type Errors Post Context: This content is adapted from our internal SME (Subject Matter Expert) curricula. Similar to knowledge drops, this content may be highly Looker specific and may become out of date in the future. The goal is to convey a high level understanding of a common SQL query issue and convey next steps for troubleshooting and resolving. What does it mean?This is a SQL error that is complaining about comparing two different datatypes. You may see a specific error that complains about DATE, TIMESTAMP; VARCHAR, INT; or really any other combination of datatypes. See your dialect's documentation for more information.An ELI5 example is if you tried to compare a number to a string. Is 1 >= 'orange' ? These ambiguities freak out computers and throw an error. For most dialects, all comparison's must be between compatible datatypes (eg a string vs a string, a number to a number, etc).The high level steps to resolve this issue would be:Identify the specific

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

107750

9 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Parsing JSON data in LookerKnowledge Drop

Last Tested: Apr 3, 2019 Looker doesn't have a native JSON field type.Workaround 1: SQL OperatorsYou'll want to use JSON parsing functions in SQL, like json_extract_path in Postgres and JSON_EXTRACT in BigQuery to extract the JSON and put it into a type that Looker can accept, like a string.There are a few Community articles where we have examples of doing this.https://community.looker.com/lookml-5/defining-json-objects-as-lookml-dimensions-2356https://community.looker.com/sql-10/using-postgres-and-json-functions-and-operators-to-parse-json-fields-1041NOTE: in BigQuery a JSON path must start with a $ followed by the index position and the string to parse, like: JSON_EXTRACT(${TABLE}.temperature_alerts, '$[0].description')Workaround 2: API ParsingHere is a cool open-source Python script which uses the Looker API to automatically detect JSON fields in the underlying database table and generates LookML for them (this is specific to Snowflake connections and may require adjusting to fit yo

100110

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

97671

9 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

96880

9 months ago

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.

93766

1 year 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.

89132

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

89090

10 months 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

79414

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.

77461

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

76670

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

72492

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

72149

1 month ago

srinija.godavarthiLooker Staff

published in Technical Tips & Tricks

SELECT list expression [field] which is neither grouped nor aggregated atKnowledge Drop

Last tested: May 26, 2020 This error can occur when you have a measure that is not doing any aggregation, most commonly- a measure of type number that is referencing a dimension. For example: dimension: id {type:numbersql: ${TABLE}.id} measure: not_working {type: numbersql: ${id} See also SQL Error: "X is not a valid group by expression" This content is subject to limited support.

71832

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

67830

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

67040

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

65311

5 months ago

Page 1 / 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.