Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,739 Topics
  • 268 Replies

1739 Topics

Department of Customer Love
Moderator
Department of Customer LoveModerator
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

342560
Department of Customer Love
Moderator
11 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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. 

172630
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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.                   

113027
n0viandi
1 month ago
ruth.mcelroy
Looker Staff
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:  

3
jasonvanderweele
4 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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.                   

72130
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

71892
sam
Looker Staff
1 year ago
sam
Looker Staff
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.                

66550
sam
Looker Staff
1 year ago
jamesnestler
Looker Staff
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

0
jamesnestler
Looker Staff
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

52620
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

52080
Department of Customer Love
Moderator
1 year ago
sam
Looker Staff
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.                   

47646
nicohein
4 months ago
sam
Looker Staff
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.                

43492
sam
Looker Staff
10 months ago
paola
Looker Staff
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.                  

42021
robertc
20 days ago
paola
Looker Staff
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

38850
paola
Looker Staff
1 year ago
A
Looker Staff
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.                   

37070
A
Looker Staff
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How do I get an image or gif on my dashboard's text tile or project readme?Knowledge Drop

Last Tested: May 15, 2020 Option 1: IF YOU HAVE A PUBLICLY ACCESSIBLE URL TO THE IMAGEYou can do this by using html in the title, subtitle, or body parameter of your text tile. Note, that the image you'd like to display needs to be on the public internet (practically speaking, what this means is that if you can open a new incognito window and go to the image's direct link, you can reach it). Note, if an image is in a Github repo and the Github repo is public, you can always add `?raw=true` to the end of the image url to get the direct link for embedding.You can use the template below, changing out the value of the img src attribute:<img src="INSERT DIRECT LINK OF IMAGE OR GIF" width="100%" height="25%" border="0" /> Option 2: IF YOU DON'T HAVE A PUBLICLY ACCESSIBLE URL TO THE IMAGEIf the image you want to display isn't currently on the public internet, you'll need to upload it to some publicly accessible location.One quick way to do this is to upload the image file to imgur.com a

36260
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

36090
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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.                   

35048
JWorkman
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How can I split a string at a certain character with a Table Calculation or Custom Field? (split part a field by delimiter)Knowledge Drop

Last tested: Jun 1, 2020 You can split a value by a delimiter in Table Calculations and Custom Fields* by using the substring and position functions.In this example:We get the first position of the character b in the name string:position(${name},"b") Then we get everything before that position:substring(${name},0,position(${name},"b") -1 ) Alternatively, you could get everything after that position:substring(${name},position(${name},"b") +1 , length(${name})) *When using Custom Fields, the looker substring() expression is converted into the Looker generated sql, and most dialects do not allow negative integers for their Substring/SUBSTR/Position functions. So you'll want to wrap this expression in a conditional if() statement to make sure you address the scenario where the search string doesn't existWhen using in Custom Field (Custom Dimension)if(position(${name},"b")=0,${name},substring(${name},0,position(${name},"b") -1 ))  This content is subject to limited support.                 

27971
I
4 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Show or hide value labels on a specific seriesKnowledge Drop

Last tested: Mar 31, 2021 There isn’t a native option in the visualization edit panel to toggle value labels on/off for individual series, but please leave feedback for the Looker product team! After authenticating into the feedback portal, you can click here for the open request!Two other options you can use below:Solution 1You can use the "Value Colors" input field to manipulate the coloring of each series by entering color codes or names and comma separating to target series in the order they appear from left to right. If you define the color as "transparent" it will hide the value label for the series! Caveat: "transparent" is only transparent to the underlying graphic, it overlaps with other value labels as "white." If you have overlapping value labels, make sure the series you want with "transparent" value labels is listed last.Solution 2Another workaround we can implement is to use the Excel-style formatting in the value format section. For example, if we write#.00%[>0.05];[&

27098
Avital
4 months ago
molly.lippsett
Looker Staff
molly.lippsettLooker Staff
published in Technical Tips & Tricks

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

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

27011
chriswragge
10 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How to join the same table twice (self-join)Knowledge Drops

Last Tested: March 22, 2021In a given explore, each join name must be unique. To self-join the same table multiple times you can use the from parameter, as mentioned in the docs, to change the "join" name of the table.In this example, we join in person to order twice, with different names and different joining dimensions.explore: order { join: person { sql_on: ${order.customer_id} = ${person.id} ;; } join: representative { from: person sql_on: ${order.representative_id} = ${representative.id} ;; }}This content is subject to limited support.                  

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

25170
sam
Looker Staff
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Can I group or bucket by table calculations?Knowledge Drop

Last tested: March 2021 No. Table calculations are computed after SQL grouping, so it's not possible to group by them.Only dimensions can be grouped on. Consider using a custom dimension for quick grouping! https://docs.looker.com/exploring-data/adding-fields/custom-measure#creating_a_custom_dimension_using_a_looker_expression This content is subject to limited support.                 

24300
sam
Looker Staff
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How can I recover a deleted Look or Dashboard if the trash is emptied?Knowledge Drop

Last tested: March 2021  As of 4.8+, all deleted content goes to the Trash Folder. It stays there indefinitely until deleted by an admin. Any content can be restored from the Trash Folder by an admin.This card talks about how to find and restore a deleted Look or dashboard with i__looker, in the case where the deleted content has also been deleted from the Trash.If you accidentally deleted a Look or dashboard AND deleted the trash, it's possible to recover this Look or dashboard using Looker's internal model, i__looker. i__looker is the model underlying the Usage Panel in the Admin section.Recovering a deleted Look that is no longer in the trash Go to the history explore of the i__looker model. You can get there by navigating to a URL of the form: https://company.looker.com/explore/i__looker/historyReplace company.looker.com with your Looker instance's specific URL. If you know a specific time that the Look was queried, filter on History - Created Time for as specific a range as possi

23242
sam
Looker Staff
4 months ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service