Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,743 Topics
  • 352 Replies

1743 Topics

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.                   

116411
C
8 hours ago
Eric_Lyons
Looker Staff
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.                  

1861
WyattNW
6 days ago
jamesnestler
Looker Staff
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.                  

3562
masutaka.net
7 days ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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.                   

3616
Bradley Keiffer
8 days ago
Michael
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. 

27182
G
8 days ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

24553
J
16 days ago
jon.allen
Looker Staff
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

3992
Mahesh
17 days ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
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

5821
Brigitte_Reda
26 days ago
sam
Looker Staff
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

1091
mitchellposluns
27 days ago
thomas.banghart
Looker Staff
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

751
mitchellposluns
27 days ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Does access to system_activity make a "viewer" user into a "standard" user?Knowledge Drop

Last tested: Aug 9, 2019 ContextIn most contracts, a "viewer" user is one who can't explore.However, in order to be able to see dashboards and Looks built off of the System Activity model, these users must be given the see_system_activity permission. Once they have this permission, they now have the "explore" tab and the ability to explore the System Activity models.Does this mean that the "viewer" is no longer a viewer, and should instead be billed as a "standard" user?AnswerEven though the 'explore' tab shows up, the user is still considered a viewer and not a standard user.​ This content is subject to limited support.                   

801
D
28 days ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

If the cache is full, then what gets dropped first?Knowledge Drop

Last tested: May 2021 Looker's default max cache size is 2GB. This can be changed with the --on-disk-cache-size=<i> startup option, where <i> is a number of megabytes (so, default 2000).If your Looker instance detects that the on-disk cache exceeds the max, then it starts purging old cache entries until the limit is no longer exceeded. The algorithm to determine which entries to purge considers the age of a cache entry as well as the last time that cache entry was accessed.  This content is subject to limited support.                 

2161
Anton
1 month ago
N
Looker Staff
nouradLooker Staff
posted in Technical Tips & Tricks

I'm trying to deploy the code using Webhooks, but I receive a 404 error.

Hi! I’m Noura TSE at Google. Have you ever get this page after trying to deploy lookML code to production? 404 Not FoundYou’ll get this error if you try to deploy via a webhook for the first time. The trick here is to deploy code manually via the Looker UI first.  If you are stuck with this error follow this. How to:Go to Deployment Manager tab and select a commit to deployClick on “Select Commit”You can choose to deploy the commit with or without tag. A manual deployment will look like this Once you complete this, you will be able to deploy via the webhooksYou can try to deploy via the browser of via command line on a terminalhttps://[Url instance name]/webhooks/projects/[project_name]/deployorcurl -i -H "X-Looker-Deploy-Secret:<Secret>" https://[url instance name]/webhooks/projects/[project_name]/deploy Cheers! Hope this is helpful! Noura

0
N
Looker Staff
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Anchor Tag on New Dashboards - HTMLKnowledge Drop

Last tested: Nov 23, 2020 On new Dashboards (non-legacy), we can anchor dashboards with the name and href html attributes!We can accomplish this in the body of text tiles. In one text tile, we can define the link to the anchor, that we click on to be taken somewhere on the dashboard by adding:<a href="#AnchorMe">Here</a> Then, we can create a text tile anywhere on the dashboard that will accept this anchored href. In that tile, we can put something like this:<a name="AnchorMe"></a> This results in a clickable link on "Here" in that first text tile, that takes us to this specific 2nd text tile with the name attribute defined. The key part of getting this to work is making sure the name attribute value, and the href #attribute value are the exact same! This content is subject to limited support.                  

5701
Cynthia168
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Embedding Looker Content in Salesforce

Looker will not be updating this content, nor guarantees that everything is up-to-date.  In order to embed content in Salesforce, you will need to have Visualforce development permissions and the ability to edit pages and objects in SFDC.This post covers an advanced topic that assumes a familiarity with embedding Looker content. Embedding dashboards and Looks in the applications and platforms where your users spend their time is one of the most powerful ways to leverage Looker. This Help Center article expands upon the concepts addressed in How to Quickly Embed Looker in the Tools and Apps You Use, to provide guidance on embedding content within Salesforce. Adding a New Visualforce Page The examples below use the private embed method, which requires each user to be signed into Looker to be able to view content. Alternatively, you can make content available to all users regardless of Looker access by using the public embed method for Looks.The first step to embedding content in Salesfor

18244
charlottemalt
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Custom Filters: 1 complete month agoKnowledge Drop

Last tested: Feb 2, 2021 ${events.created_date} >= add_months(-1,trunc_months(now()))AND ${events.created_date} < add_months(1,add_months(-1,trunc_months(now()))) This content is subject to limited support.                 

1951
thinh96-1657593683
1 month ago
Peggy_Beard
Peggy_BeardMember
published in Technical Tips & Tricks

How do you use refinements to change a view in only one explore in a model?Knowledge Drop

Last tested: Jan 19, 2021 Use the ordering of refinements, as mentioned in this articleLogical example: Model file includes view file for view: aand hasexplore: bjoin: aview: +adimension: newfieldexplore cjoin: aOnly explore c should include the newfield dimension. This content is subject to limited support.                   

3791
Taha
1 month ago
Morgan
Looker Staff
MorganLooker Staff
published in Technical Tips & Tricks

Can you change the background color of a dashboard?Knowledge Drop

Last tested: Oct 19, 2020 Only for Embed dashboards.For Dashboards-Next, it is one of the settings under Dashboard Page in Custom Themes (note that this is a license feature, so it will need to be enabled on an instance).For regular dashboards, there is an "embed settings" option that lets you edit certain options for an embedded dashboard. There are LookML dashboard parameters too. The options look like this:You can't do this for dashboards in the Looker UI. This content is subject to limited support.                  

23631
Santiago Deniz
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why are my series colors and other visualization settings randomly changing?Knowledge Drop

Last tested: May 6, 2020 Is it a pivoted query? If so, it could be a case of “When the clock strikes midnight the visualizations come alive with a mind of their own.”The series editor is based around the pivot value of the series, so any visualization settings you have set on the pivoted series (colors, vis type, y-axis settings, etc.) will revert to default settings if those pivot values change. That is, when the pivot values returned by the query change based on new data, different filters, etc., the saved vis settings on the original pivot values will not be carried over to the new pivot values.Although this behaviour can be confusing, it is intended functionality, so you can go ahead and add your vote for this feature request if you are thrown off by this behaviour. The workaround is to turn the pivoted series into table calcs using pivot_index() to make sure the series names never change. This content is subject to limited support.                  

2232
A
1 month ago
nathaniel.thompson
Moderator
nathaniel.thompsonModerator
published in Technical Tips & Tricks

Does using derived_table or sql_table_name in an extending view override the original view's derived_table or sql_table_name?Knowledge Drop

Last tested; Mar 11, 2021 Yes! Even though usually, extends will only replace parameters that have the same name definition, sql_table_name and derived_table are special, since they're mutually exclusive. They cannot both be used at once in a single view file. Therefore they follow standard extends logic as if they were the same parameter, and the losing view's table parameter is ignored.Example: When using an explore with view_a, my_fancy_table is used in the generated SQL. When using an explore with view_b, the derived table with my_favorite_table is used in the generated SQL.view: view_a {sql_table_name: my_cool_schema.my_fancy_table ;;...} view: view_b {extends: [view_a]derived_table: { sql: SELECT * FROM my_cool_schema.my_favorite_table ;;}...}  This content is subject to limited support.                   

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

108612
J
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

In what timezone does the table calculation now() return?Knowledge Drop

Last Tested: Jul 3, 2018 In databases that support timezone conversion, now() returns the time in the timezone set in the Query Time Zone property of the database connection for the explore.In the case that the database does not support timezone conversion, now() returns the time in the timezone of the Application Time Zone in the general settings admin page. This content is subject to limited support.                 

1121
vmd98
1 month ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How do BigQuery OAuth connections work with GCP IAM?Knowledge Drop

Last tested: Jun 26, 2020 BigQuery customers have the option of using OAuth for BigQuery connections. (The other option is service accounts). These connections are "directly integrated" with GCP Identity and Access Management (IAM). This provides user-level authentication (OAuth) and authorization (IAM). Every query is run as if that user was running the same SQL when directly logged in to the BQ console.Key features and constraints of OAuth + IAMUsing OAuth as the authentication mechanism ensures that IAM permissions are enforced per user. Every query is run as if that user was running the same SQL after they logged in to the BQ console. Examples of per-user security controls include: Only having access to the correct datasets Only having access to the correct authorized views Applying row level controls implemented using authorized views Per-user caches are available Per-user BQ resource limits are available PDTs and shared caches are disabled under per-user authentication This

4301
Q
1 month ago
V
Looker Staff
vishal.dharmadhikariLooker Staff
published in Technical Tips & Tricks

Can I update my dashboard with a new custom slug using update_dashboard?Knowledge Drop

Last tested: Feb 10, 2021 Yes, we can update the dashboard with a custom slug but please note dashboard slugs are required to be 22 characters long.If we try to use the update_dashboard endpoint with a slug that's less than 22 characters, we'll see the following error message. # Node SDKconst dashboard = await lookerSDK.ok(lookerSDK.dashboard("123"))const test = await lookerSDK.update_dashboard("123", {slug: "slugtest"});# Results in this errorerror: {message: 'The first argument must be one of type string, Buffer, ArrayBuffer, Array, or Array-like Object. Received type undefined',type: 'sdk_error'}, This content is subject to limited support.                  

3763
R
1 month ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Why do I have more rows in my drill table than my count_distinct measure indicates?Knowledge Drop

Last Tested: March 15, 2021 If multiple rows have the same value in the column which is being aggregated with a count distinct, you could see this behavior. The drill will show distinct combinations of the dimensions you've indicated in that measure's drill_fields parameter, which might be more numerous than the distinct values counted by your count distinct aggregation.Example:If you have a count distinct on ID that shows 1 in the column, and if more fields are selected in the drill down, you could have this:id value1 A1 B1 CThere is 1 distinct ID, but is getting expanded in the drill.Also, in cases where count is being filtered using a CASE WHEN statement in the sql parameter of the field you're trying to drill into, this CASE WHEN condition doesn't get factored in to the drill filters, which can also cause a discrepancy. This Community post describes using a filtered measure instead of a CASE WHEN to resolve this issue.  This content is subject to limited support.                  

5331
divya_bennett
1 month 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