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

Recently active

Most replies Most views Newest first

samLooker Staff

published in Technical Tips & Tricks

What HTML tags are allowed? (HTML Sanitization)Knowledge Drop

Last tested: March 2021 This doc lists all of the allowed HTML tags that can be used in html blocks in Looker. This content is subject to limited support.

3351

12 hours ago

lauren.boltzCommunity Admin

published in Technical Tips & Tricks

Why do Nulls Still Appear in My Measures After Wrapping the SQL: Parameter in Coalesce()?Knowledge Drop

Example a measure of type: average (or Sum/Count/etc) is producing Nulls in an Explore. I want to display 0 instead of Null. measure: my_measure {type: averagesql: ${orders} ;;} A gut instinct may tell us to wrap the sql: parameter in a coalesce()function:measure: my_measure {type: averagesql: COALESCE(${orders},0) ;;} However, Nulls will still display for this measure in the results table.Why?The coalesce is occurring before the aggregation. The average will return Null for values where there are no rows, so the inner coalesce never has a chance to operate.How do we show 0 instead of Null?We'll need to create a new measure of type: number (which does not perform any aggregation) to wrap the measure in a coalesce(): measure: my_measure_coalesced {type: numbersql: COALESCE(${my_measure},0) ;;} Now, the measure will display 0 in place of any Null that is returned! But a pivot is forcing the nulls... Then we won't be able to use the above approach, because the nulls are coming through to

8661

19 hours ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

samLooker Staff

published in Technical Tips & Tricks

Can I integrate R and Looker? (LookR)Knowledge Drop

Last tested: Oct 25, 2019 We do have an open source R library called LookR for API 3.0. However, it's not supported and hasn't been updated recently (in the past 2+ years, use with caution). It calls the Looker API to get data into R.There are talks of updating LookR to fit the new API versions, but it is still in its early stages.This is another package that was put together by different people, not generated in the same way as our other currently supported SDK: https://github.com/looker-open-source/sdk-codegen and github.com/Rich1000/lookrAt this point in time, we'd recommend using Python if it's an option. This content is subject to limited support.

5361

8 days ago

Pieter_DeVriesLooker Staff

published in Technical Tips & Tricks

Can I change value label alignment (vertical & horizontal) in stacked bar/column charts?Knowledge Drop

Last tested: May 18, 2020 No, that is currently not possible at the moment.If that is something you're interested in, please share your use case with Looker Product team via Pendo feedback. This content is subject to limited support.

1341

17 days ago

mukulmunjal-1646050669Looker Staff

asked in Technical Tips & Tricks

"Field Usage" System Activity explore retention

Is the retention of Field Usage Explore (<your-hostname>/explore/system__activity/field_usage) is same as History explore i.e. `90 days`?

18 days ago

adina.katzLooker Staff

published in Technical Tips & Tricks

Filter a column on multiple values from another columnKnowledge Drop

Last tested: Sep 7, 2019 Say you want to filter a column by multiple values in a "sub column", i.e you have a column order id's and a column product id's, and you want to filter for order id's that contain product id A AND product id B.The issue: You can't use a custom filter because that filter is a row based filter and won't account for multiple row values of product id for one value of order id.The bad solution: You can use a PDT to filter on product id A then filter on product id B in the explore (or in a derived table with the filter for A as a sub select)The better solution: A group concatenation aggregate function! How this works: The aggregate function works like any other function and will concat values based on the grouping. Implementation: We can either useApproach 1: Create measure type listmeasure: product_id_list { type: list list_field: product_id;;} Approach 2: Create measure of type string and use appropriate sql dialect for group_concatmeasure: group_concat_product_

12843

29 days ago

samLooker Staff

published in Technical Tips & Tricks

How do I make my dashboard timezone listen to the timezone defined in my embed URL?Knowledge Drop

Last tested: Nov 2020 Enable User-Specific Timezones in the Admin panel. Add the user_timezone as one of the embed URL parameters when generating the embed URL. After the embed URL loads the dashboard, the embed user can switch to Viewer Timezone in the timezone dropdown menu at the top right. But what if i want to have the embed dashboard loaded with the user-specific timezone by default?Steps 1 and 2 stay the same. Instead of having the embed user switch the timezone in Step 3, use one of the following options.Option 1: Save the dashboard with the default timezone "Viewer Timezone". This will use the user's timezone as default for both embed users and non-embed users.Option 2: We can add &query_timezone=user_timezone as part of the dashboard URL. This will make the user's timezone selected by default even if it is not the saved default timezone for the dashboard. Note that the user_timezone is one of the embed URL parameters, but the query_timezone is not an embed URL param

4491

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

What is the `POST webhooks/projects/[project]/deploy` call I see in logs?Knowledge Drop

Last tested: May 29, 2020This is the call Looker makes when creating and pushing a new branch This content is subject to limited support.

291

1 month ago

jamesnestlerLooker Staff

published in Technical Tips & Tricks

How Looker does timezones and how to troubleshoot themKnowledge Drop

Last tested: Feb 1, 2021 Timezones, yikes!You may need to troubleshoot timezones to answer questions like:Why isn't Looker converting timezones correctly? Why is the timezone CONVERT SQL wrong? Inaccurate? Is there a timezone conversion bug? My finance team says timezone conversions are incorrect because order X is missing from our Explore. Why is Looker not doing this correctly?The goal is this article is to help you understand how Looker does timezones and how that interacts with your database.How Looker does timezonesConnections Panel, Database Timezone: This is the timezone Looker THINKS dates are stored in. Will show up as one of the parameters in the SQL Looker generates to convert_timezone in generated SQL, on most dialects. Connections Panel, Query Timezone: This is the timezone Looker will attempt to convert timestamps TO. Shows up as the other parameter in the SQL Looker generates to convert_timezone. User Setting: User Timezone; "User Specific Timezones" related. This will r

36762

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

How can I use an apostrophe in a user attribute value?Knowledge Drop

Last Tested: Jan 21, 2019Just put a \ before the apostrophe!ex. Default value: O\'Donnell This content is subject to limited support.

872

1 month ago

vishal.dharmadhikariLooker Staff

published in Technical Tips & Tricks

`Comparison method violates its general contract!` error when running a query.Knowledge Drop

Last tested: Oct 6, 2020 This is a pretty weird error that's popped up a couple times. This usually appears when we've pivoted data and there's a conflict in the inputs which causes a Java issue (Stack Overflow).One option to resolve this issue is to reduce the number of columns in the Explore. This content is subject to limited support.

3021

1 month ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Splitting Strings into Rows in the Absence of Table-Generating Functions

Looker will not be updating this content, nor guarantees that everything is up-to-date. IntroductionMost SQL dialects don't support robust table-generating functions. There are a few common use cases for table-generating functions, such as creating a sequence of numbers or dates, un-nesting a JSON object or array into rows, or splitting a string on a delimiter into rows.In this article, we'll show an example for the last use case: Taking a string containing n distinct comma-separated values and splitting it into n unique rows.SetupSuppose we have a table of products containing id, name, and tags: productsid name tags 123 Scrunch Cloth Pants Set Pants, Throwback, 80s 1287 Le Suit Citrus Breeze Skirt Suit Skirts, Dresses, Business Casual 486 MaxStudio Plaid Romper Jumpsuits, Casual, Rompers Our goal is to un-nest the tags to create a table to map products to tags. We will assume that a table of numbers already exists in the database, though this can be created using t

31251

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

130261

1 month ago

Eric_LyonsLooker Staff

published in Technical Tips & Tricks

Use Table Calculations to Get Most Recent Line Item When User Has Duplicate Rows (Non-subquery version)Knowledge Drop

Last tested: Mar 19, 2020 If you have line items in a table with duplicate ids, but only wants the most recent items or highest items many times you will try to use a SQL subquery to SELECT MAX(DATE) for the given parameter, but you can create this with table calculations in an explore.First, sort the explore by the ID. Then, apply a secondary sort to the date column (descending).Then, we can create a table calculation that checks if the value below the id is a duplicate.Then, we check the date column for duplicates. This content is subject to limited support.

2061

2 months ago

jamesnestlerLooker Staff

published in Technical Tips & Tricks

How to: Multiple user attribute values (OR logic)Knowledge Drop

Last tested: May 20, 2019 Use a String Filter (advanced) user attribute type and input the user attribute values with the following syntax:'value1','value2' Each value should be wrapped with single quotes and delimited with a comma. Make sure there are no white spaces between the values. This content is subject to limited support.

4092

2 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How are files sent to SFTP and S3 buckets named?Knowledge Drop

Last tested: May 31, 2019 All file names look like scheduleName_timestamp_hash, where hash is a random 6-character identifier and the timestamp follows the pattern YYYY-MM-DDTHHMM (e.g. 2019-05-31T0933).There is a feature request ticket to be able to customize the file name of files sent via schedules: Change file name for data sent in schedules This content is subject to limited support.

4006

2 months ago

MichaelMember

published in Technical Tips & Tricks

Important: Supportability of Knowledge Drops

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified. We have spent years building up this treasure trove of insightful Looker tips, tricks, and how-to’s, but given how quickly Looker has evolved, we never expected this content to stay bleeding-edge forever. Think of these Drops as point-in-time insights that were once important enough for us to document. That said, there’s a TON of extremely valuable and useful pieces of Looker knowledge here! We hope it inspires you to explore, learn, and engage with your fellow community members! If you have questions on these articles, please reply on the post for Community help! Plus, if you’re looking for officially supported resources, you can always visit our Help Center and review our Docs.

30022

2 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Using Liquid Split and Array Filters to Format Lists

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post covers advanced subject matter and assumes good, pre-existing knowledge of Liquid and LookML. The Problem In data, objects often have lists of attributes associated with them. For example, a user's profile can contain multiple cities, states, or countries, depending on how often they have changed addresses. A common challenge is organizing and presenting lists of attributes in a user-friendly and digestible way. This article addresses how to render lists into easily readable formats and how to make them more interactive. To achieve this, we will use Liquid, a templating language developed by Shopify, in conjunction with the html and link LookML parameters. The Solution In our e-commerce data set, we have a field called "City List Raw." This is a type: string field that outputs a list of every city where users live now or have lived in the past. In our Explore below, we have the user citi

29393

2 months ago

jon.allenLooker Staff

published in Technical Tips & Tricks

How to build a dynamic link parameter for different dashboard ID between dev and production environmentsKnowledge Drop

Last Tested: Jun 25, 2020 ProblemUsing relative links (/dashboards/410) vs. absolute links (https://abcdefg.looker.com/dashboards/410) will correct for differences in the base domain, but will not accommodate for differences in dashboard ID between dev and production instances.SolutionIf a dashboard was moved from one instance to another using Gazer, the dashboard ID may be different, but the dashboard slug will remain the same. So we can use the slug in place of the id directly in the link.For example you can do gzr dashboard cat 410 --host myinstance.looker.com and get:{"content_metadata_id": 2997,"description": "This is Katie's cool dashboard","hidden": false,"id": 410,"query_timezone": null,"readonly": false,"refresh_interval": null,"refresh_interval_to_i": null,..."favorite_count": 0,"last_accessed_at": "2019-06-28 15:23:00 +0000","load_configuration": "cache_run","lookml_link_id": "girls_at_looker::WB_TEST","show_filters_bar": true,"show_title": true,"slug": "KoDXRuaeLDQCuQ0rt4iH

4562

2 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I change the visualization of my drill fields? What criteria does Looker use to set which a visualization will be used in a drill set?Knowledge Drop

Last tested: Sep 6, 2019 It's not possible to choose a visual drilling type via the UI, but there are implicit rules that say what the visualization will be. There is a feature request requesting ability to change them at will.WorkaroundYou can use the method in this Help Center article to hard code the viz type for drilling on a field: https://help.looker.com/hc/en-us/articles/360023589613--More-Powerful-Data-DrillingThe Rulesif a dimension is the primary key OR (the first column is a dimension AND it’s name is id) OR (the first dimension’s name is _id) if there is only 1 record then “single_record” viz otherwise we’ll choose a “table” if the first dimension contains “albersUsa” data and there’s only 1 measure column then the type should be “looker_geo_choropleth” if any of the dimension contains location data then go for “looker_map” if any of the dimension contains map layer data and any measures/dimensions are numeric then go for “looker_map” if there’s no dimensions or measures

6681

2 months ago

samLooker Staff

published in Technical Tips & Tricks

Why are my PDTs building on multiple connections?

Last tested: June 2021 A PDT will build on every model that it is included in. So, if you have several models with include "*.view", then each of those models will include all PDTs that you've defined.A good solution is to adopt a naming convention for your PDT views. For example, you could make sure your BigQuery PDTs all begin with bq so that you can write:include "bq*.view" in your model that includes a BigQuery connection.Then, once you have your PDTs being included that way, you can adopt a similar naming convention for files you want to include in everything (like everywhere_* ) or include them individually.Here are some other common reasons PDTs would build for multiple models: - The PDT is included in multiple models that have different connections.- The PDT uses a datagroup that is defined differently in different models.- The PDT is included in multiple models with different case sensitivity settings- Differences in a dependency PDT between one model and another. This cont

1251

2 months ago

thomas.banghartLooker Staff

published in Technical Tips & Tricks

Will a PDT build for each model that includes it?Knowledge Drop

Last tested: Jul 8, 2020 In most cases if one PDT view is included in multiple models which all share the same connection, the PDT is built once and is shared by multiple models.There are some cases where a PDT will be built for each model even when those models share a connection. Some reasons that warrant multiple builds:The PDT is included in multiple models that have different connections. The PDT uses a datagroup that is defined differently in different models. The PDT is included in multiple models- with different case sensitivity settings Differences in a dependency PDT between one model and another. Note: Admin > PDT will list out all the models that the PDT is shared between (the models have the same datagroup definition and the same connection). However, as mentioned above, the PDT is built only once for one model and the rest of the models will just use that build. That is why, in the system activity PDT_builds explore, you'll see that the PDT belongs to only one of the

861

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

The Kitchen Table

What HTML tags are allowed? (HTML Sanitization)Knowledge Drop

Why do Nulls Still Appear in My Measures After Wrapping the SQL: Parameter in Coalesce()?Knowledge Drop

Converting Shapefiles to TopoJSON

How can I create a new project via the API?Knowledge Drop

Selectively highlight data points in visualization using a filterKnowledge Drop

Can I integrate R and Looker? (LookR)Knowledge Drop

Can I change value label alignment (vertical & horizontal) in stacked bar/column charts?Knowledge Drop

"Field Usage" System Activity explore retention

Filter a column on multiple values from another columnKnowledge Drop

How do I make my dashboard timezone listen to the timezone defined in my embed URL?Knowledge Drop

What is the `POST webhooks/projects/[project]/deploy` call I see in logs?Knowledge Drop

How Looker does timezones and how to troubleshoot themKnowledge Drop

How can I use an apostrophe in a user attribute value?Knowledge Drop

`Comparison method violates its general contract!` error when running a query.Knowledge Drop

Splitting Strings into Rows in the Absence of Table-Generating Functions

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

Use Table Calculations to Get Most Recent Line Item When User Has Duplicate Rows (Non-subquery version)Knowledge Drop

How to: Multiple user attribute values (OR logic)Knowledge Drop

How are files sent to SFTP and S3 buckets named?Knowledge Drop

Important: Supportability of Knowledge Drops

Using Liquid Split and Array Filters to Format Lists

How to build a dynamic link parameter for different dashboard ID between dev and production environmentsKnowledge Drop

Can I change the visualization of my drill fields? What criteria does Looker use to set which a visualization will be used in a drill set?Knowledge Drop

Why are my PDTs building on multiple connections?

Will a PDT build for each model that includes it?Knowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded