Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2110 Topics

Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Can I use Table Calcs to find the total number of rows in my result set? (max)Knowledge Drop

Last Tested: Jan 21, 2019 Yes, you can use the formula below to see the max number of rows in your result set.max(row()) This content is subject to limited support.                 

250
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

If I have multiple instances (prod, staging), are user accounts with the same email tied across instances?Knowledge Drop

Last Tested: Sep 6, 2019 No! Even if using the same email address, a user account on Instance A is not the same as the user account on Instance B. Even when a user has multiple instances (i.e. company.looker.com, company-staging.looker.com, companyonprem.looker.com)Same goes for passwords. User could have set up different passwords for each instance.Pro Tip: If a user does want standardized credentials across instances, you should consider another authentication provider like SAML or LDAP. This content is subject to limited support.                 

460
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Is it expected that Looker will round very large numbers? (lose precision)Knowledge Drop

Last Tested: Jul 3, 2018 Yes, the limitation will happen at around 16-17 digits and it will round the additional integers into "0"s. For example:5820847388981125942 becomes5820847388981126000Overall it is a precision limit of javascript.WorkaroundsUse {{ rendered_value }}dimension: id {primary_key: yestype: numbersql: ${TABLE}.id ;;html: {{ rendered_value }} ;;}  Use two dimensions- dimension: string_valuetype: stringsql: ${TABLE}.myfieldorder_by_field: int_value - dimension: int_valuetype: numbersql: ${TABLE}.myfield SQL RunnerSQL Runner will also render the lost precision numbers. The workaround for SQL Runner is to have the value casted as a STRING in the query itself. This can vary dialect to dialect. This content is subject to limited support.                 

1380
Department of Customer Love
Moderator
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Git Lock ErrorsKnowledge Drop

Last tested: March 2021  If you get an error with a git operation that mentions a lock, there is probably a lock file.These files are usually temporary and will go away if you wait a sec and try the operation again.If you have command line access to the git repository, you can run git gc to figure out where the lock file is - you may see something like:fatal: Unable to create '/mnt/efs/models/<project-name>/.git/refs/heads/master.lock': File exists.If no other git process is currently running, this probably means a git process crashed in this repository earlier. Make sure no other git process is running and remove the file manually to continue. This content is subject to limited support.                  

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

How do I visualize a dimension as a measure?Knowledge Drop

Last tested: March 2021 You may be wondering how to plot a dimension on the y-axis of a visualization.Quick SolutionThe quick way to do this is with a table calc involving an existing measure.${dimension} + ( ${measure} * 0)A similar approach can be taken with this table calc for a String dimensionif(is_null(measure),string_dimension,string_dimension)More details on this method in the Help Center article here. Proper SolutionThe right way to do this is with an aggregate in LookML.measure: sum_dimension {type: sumsql: ${dimension} ;;}This content is subject to limited support.                

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

How do I fix REJECTED_NONFASTFORWARD errors?Knowledge Drop

Last verified: March 2021 This can happen when your branch on the remote is ahead of your local branch and you go to deploy.Solution 1: Shift click the git drop down and hit "pull remote changes".Note that this will overwrite any local changes (anything you've saved without committing) since your last commit.Solution 2: If pulling from remote doesn't work due to a conflict with files, and it's a relatively small amount of files causing a conflict, save them externally, deleted them from the project, and then try pulling remote changes again. Then you can add the files back by dragging and dropping them into the project. This content is subject to limited support.                

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

Will "Clear Cache and Refresh" affect filter suggestions?Knowledge Drop

Last tested: March 2021 No. The suggestions have their own cache that cannot be manually cleared from the explore page. If you need to clear cache for suggestions, here are some other options: If the explore is cached using a datagroup with a sql_trigger, you can manually reset the cache for the entire datagroup in the Admin - Datagroups panel. Note this will bust cache for all queries persisted using that datagroup. You can set suggest_persist_for to "0 seconds" to bust filter suggestion cache for that field. The docs have more info on using suggest_persist_for to set the cache expiration time.  This content is subject to limited support.                

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

Can I drill down on a drill down?Knowledge Drop

Last tested: March 2021 Yep! As long as the fields have drill_fields defined, you can continue drilling on the drill modal to infinity and beyond (the drill modal will reload upon each consecutive drill). This content is subject to limited support.                

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

Can I remove sorts (order by) from the SQL?Knowledge Drop

Last tested: June 2020 This is not an option by default in the UI. There are some workarounds though: Workaround 1 (Downloads): There is a checkbox to remove sorting in the download modal.Workaround 2 (null dimension):  This dimension will generate ORDER BY NULL which will help to optimize performance.dimension: no_orderby { type: number sql: null ;; }Workaround 3 (API): Using either the create_query, run_inline_query or create_query_task endpoints, pass in ["__UNSORTED__"] for the `sorts` parameter. This content is subject to limited support.                

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

Does clearing cache clear the drill down cache?Knowledge Drop

Last tested: March 2021 No. When you clear cache, it only clears cache for the current query, not any cached results for the drill down.Workarounds The recommended workaround is to have a Datagroup set up on the explore, so that you're not relying on manual cache clearing. If you need to do this manually, then you must "explore from here" on the drill down and then "clear cache and refresh" on that explore.  This content is subject to limited support.                

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

When would my Remote Origin URL start with `ssh://`?Knowledge Drop

Last tested: March 2021  When you're using a non-standard Git port. From git docs:URLs with custom or non-standard git ports can be used with Looker by adding the non-standard port number to the git URL. For example: ssh://git@corporate.git.server.com:22/myorganization/myproject.gitPlease note that you must include the ssh:// for this to work properly. This content is subject to limited support.                

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

SQL Error: "X is not a valid group by expression"Knowledge Drop

Last tested: March 2021 This SQL error means that that database is trying to group on something that it can't. Usually, this means that there are aggregates in a dimension definition.To troubleshoot, first try to isolate the field that throws the error. Then take a look at its LookML definition. One example of aggregation inside dimensions that could cause this is:dimension: bad_dimension {type: numbersql: SUM(${other_dimension}) ;;}The solution is to only do aggregations in measures. This example should instead be a measure:measure: sum_measure {type: sumsql: ${other_dimension} ;;}If you need aggregation to happen in a dimension, so that you can pivot or group by it for example, then see dimensionalizing a measure. This content is subject to limited support.                

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

Using liquid 'split' to iterate (loop) over a stringKnowledge Drop

Last tested: May 2020 This comes in handy when you has a comma-separated list and you want to format it or apply links to each comma-separated value.For example:List apple, banana orange, apple, banana Since the "list" fields can have either 2 values or 3, we can easily use the split function to format the list with bullet points regardless of how many values there are.dimension: list { type: string sql: ${TABLE}.list ;; html: {% assign words = {{value}} | split: ',' %} <ul> {% for word in words %} <li> {{ word }} </li> {% endfor %} ;; }This generates a bullet list on our original comma-separated list.Link to Shopify Documentation on split This content is subject to limited support.                

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

Can I choose the value for comparison in a Single Value Visualization?Knowledge Drop

Last Tested: Jul 3, 2018 Not by default, but we can use the offset function to adjust what value is shown. For example:#comparison table calculation columnoffset(${column_name},-2) You can adjust the "-2" as either a positive or negative value to customize the value. This content is subject to limited support.                 

2030
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How can I make one column have a conditional format style based on another column's value?Knowledge Drop

Last Tested: Jan 21, 2019 This is possible using Liquid in the html: of a given LookML field. The example is not exactly highlighted in this community article, but it can help for a reference point. For example:Articlehtml:{% if value == 'Shipped' or value == 'Complete' %} Using Liquid _valuehtml:{% if value == 'Shipped' or orders.state._value == 'California' %} Where _value is a Liquid variable that lets you refer to other values in the same row. See this page for more details. This content is subject to limited support.                  

5710
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Are merge results cached?Knowledge Drop

Last Tested: Dec 6, 2018 As of 5.12 there is an option to Clear Cache and Refresh on the merged results page. Before 5.12, you had to click "Explore From Here" for each query and "Clear Cache & Refresh" each explore individually. Re-run the merge results and your data will reflect the refresh. This content is subject to limited support.                  

460
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

If we don't specify a `max_cache_age`on a datagroup, will it default to the 1 hour?Knowledge Drop

Last Tested: Jan 21, 2019 According to the datagroup docs, the cached results will be busted the next time the sql_trigger_value changes. So, no, the cached results will not expire in one hour.The cache age will, however, be affected by explore-level or model-level max_cache_age or persist_for values. This content is subject to limited support.                 

970
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How to create a running/rolling anything function (sum, mean...) down a column or across a pivot rowKnowledge Drop

Last tested: Jan 21, 2019 You can do this using a custom table calculation formula.Down a column:offset_list(${view_name.field_name},-1*row()+1,row()) Across a pivot row:pivot_offset_list(${view_name.field_name},-1*pivot_column()+1,pivot_column()) Just wrap your aggregation function around one of these formulas, and you're good to go!Examples:Running standard deviation down a column (yes, you can use this with any aggregation function available for table calculations!)stddev_samp(offset_list(${view_name.field_name},-1*row()+1,row())) Running total across a pivoted row:sum(pivot_offset_list(${order_items.count},-1*pivot_column()+1,pivot_column()))  This content is subject to limited support.                 

7870
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why is my percent or average measure showing zero (or rounding to an integer) aka where are my decimals?Knowledge Drop

Last tested: May 1, 2020 Here's a Help Center article to explain this question.ExplanationIf you divide two integers in SQL, you get an integer. Thus 1/2 = 0.WorkaroundThe workaround is to cast one integer to a float. An easy way to do that is to multiply by 1.0.It is very important that the multiplying by 1.0 happens before the division operation.Working examples:1.0 * ${count} / ${sum}${count} * 1.0 / ${sum}${count} / ( ${sum} *1.0)What won't work:${count} / ${sum} *1.01.0 * ($[count}/ ${sum})Note for Druid Database: ${count} * 1.0 / ${sum} will not work. Put 1.0 to the denominator instead ---> ${count} / (${sum} *1.0) Redshift Edge CaseDividing by two SUMs in Redshift seems to default to 4 places of precision, as noted in this Stackoverflow article. The workaround is to cast one of them to a higher numeric datatype. ${sum1}::numeric(19,6)/${sum2}This also happens to measures of type:average on Redshift when the field being averaged is an integer.Presto Edge CaseUsing 1.0 * ${vari

4770
Department of Customer Love
Moderator
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

What's the longest allowed filter? Is there a limit on characters or values?Knowledge Drop

Last tested: March 2021 In the Looker product/code, there are no maximums on filter values or length. Theoretically, you can enter as many as you want. However, Looker uses Puma, which limits the url characters to 10240 (source: Puma Code).In the browser, a long URL can break the browser. Stackoverflow cites ~2,000 characters as the lowest limit for a browser (IE/Edge). All other browsers support URLs with > 8000 characters. As long as the URL is hashed (like in an explore), this is not a problem, but dashboard filters are simply added to the URL at the time of this writing. These problems can be solved by putting the values into a yesno dimension and filtering on that. An example:dimension: id_matches_value_list { type: yesno sql: ${id} IN (value1, value2, ... );; }In the database, there is usually a limit on query length, which is relevant since the filters usually go into the WHERE clause. A common limit is 2^16 characters (65336). There's no known workaround for this. This conte

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

Can you do nested if in liquid?Knowledge Drop

Last tested: Jan 21, 2019 Yes! Source though it may be less confusing if move one of the conditions into a new field like a yesno:{% if template == "index" or template == "page" %}{% assign correct_template = true %}{% endif %}{% if correct_template and settings.slideshow_enabled %}<div class="container main content">...</div>{% endif %}  This content is subject to limited support.                  

720
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why do I see "no results" when drilling from a measure?Knowledge Drop

Last tested: May 14, 2020 When I drill into a measure, such as a count, I see what is filtered on, and then the query shows "no results"This can occur when there is an error in one of the fields being retrieved. Try "explore from here", or check the Admin -> Queries panel to see if there are any queries with errors. From there, the query can be opened in SQL runner to debug it.It could also be the case that there just aren't results. Check the drill_fields parameter for the measure and verify that the generated SQL should produce results in their data. (You can get the generated SQL for the drill in the Admin > Queries panel).Or if you enter a new row limit, hit run, then you can now see the results. This could be due to the drill querying having cached results. This content is subject to limited support.                 

5410
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why is Looker not writing the Join condition in the generated sql? (sql_on)Knowledge Drop

Last tested: Jan 21, 2019 In all likelihood the LookML model syntax is using sql: INSTEAD of sql_on: Confirm that in your join you are using sql_on and not sql.For example:explore: deals {join: fact {relationship: one_to_onesql_on: ${fact.offer_id} = ${deals.offer_id} ;;}join: lead {relationship: one_to_manysql_on: ${lead.offer_id} = ${deals.offer_id} ;;}join: provider_lead {relationship: one_to_onesql: ${lead.redirect_id} = ${provider_lead.redirect_id} ;;}  The 3rd Join statement is using sql: and that will cause Looker to generate the following sql:FROM dealsLEFT JOIN view.lead AS lead ON lead.offer_id = deals.offer_idlead.lead_id = provider_lead.lead_id  It should be [LEFT JOIN] lead.redirect_id = provider_lead.redirect_id.If you wanted to customize your join logic with sql make sure to write that in the parameter, such asjoin: provider_lead {relationship: one_to_onesql: RIGHT JOIN ${provider_lead.lead_id} ON ${lead.lead_id}= ${provider_lead.lead_id} ;;}  Otherwise, just change the

1560
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why are there dashboard links in my drill menu when I haven't defined any?Knowledge Drop

Last tested: Jan 22, 2019 Double check to see if you have the Labs feature Dashboards in Drill Menus enabled. It's an awesome feature that can tell if a dimension can be filled in as a filter in a dashboard and then suggests the drill based on that! This content is subject to limited support.                  

510
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why isn't my dynamic liquid label changing when I change the filter value?Knowledge Drop

Last tested: Aug 30, 2018 Is it changing in the visualization? Keep in mind that when a value of a liquid variable used in the label parameter is changed on the explore page, the change will be reflected in the visualization, but NOT in the Results table or in the field picker.Example: You have a label parameter defined like this: label: "{% if _filters['orders2.status'] == ''%}'default'{% else %}{{_filters['orders2.status']}}{% endif %}" Before filtering on status (Results table and vis both say "default"):After filtering on status (Visualization now says "test"; Results table still says "default"): This content is subject to limited support.                 

880
Department of Customer Love
Moderator
1 year 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