Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,744 Topics
  • 406 Replies

1744 Topics

Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Missing values for 'VIEW_NAME.FIELD_NAME' were not filled. It is unclear what values to fill for this field.Knowledge Drop

Last tested: Nov 8, 2020 When you receive a report with an error message like below: One of your tiles are hitting this error:Missing values for 'VIEW_NAME.FIELD_NAME' were not filled. It is unclear what values to fill for this field.  We won't be able to see this error message on a dashboard, but when you go to the edit page of the tile, you will see that message there:According to Chris's response in this community post, this error occurs when dimension fill is being applied to a dimension that cannot use dimension fill. Most commonly, the dimension previously defined as a yesno, tier, date, or case type dimension but was later changed to a string or number type, you might encounter this error.SolutionIf dimension fill is turned on, you can turn it off by selecting Remove Filled in Values from the gear menu for that column, which should remove the error message.Find this error message in the edit page of your tile: Missing values for 'VIEW_NAME.FIELD_NAME' were not filled. It is uncl

11520
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Dynamically change a referenced pivot column based on latest date shown in the data tabKnowledge Drop

Last tested: Nov 30, 2020 GoalWhen we use a pivot table, sometimes we want to display ONLY most recent date/week/year columnChallengeBut Number of pivoted columns are changing based on filter definitions. So we don't want to hard code to define reference column with pivot_index function. How can we dynamically change referenced pivot column based on latest date shown in the data tab?e.g.When I run query without filter(1), I have 5 pivoted columns (year 2013, 2014, 2015, 2016 and 2017). In this case, we want to display user counts only for year 2017. However, if end-user filter out data(2) with the city name River Falls, we see 3 pivoted column in table chart. In this case, we want to display user counts for year 2016.(1) Without Filter value:(2) With filter value:Solutionpivot_where(${users.created_year}= pivot_index(${users.created_year},max(pivot_row(pivot_column()))), ${users.count}) Here is a template!pivot_where(${PUT YOUR TIME/DATE/WEEK/YEAR FILED} # I used YEAR for this sample=

2140
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

What is pdt_build_failure_cleanup ?Knowledge Drop

Last tested: Jan 20, 2021 When a build fails or is detected as being canceled, we drop the table that may have been partially created. We do this to make sure the name is available for a subsequent build attempt. This content is subject to limited support.                  

30
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Custom SMTP: Failed to send mail: 501 Sender syntax errorKnowledge Drop

Last tested: Nov 30, 2020 When we see this error message on SMTP setting page, more likely to be due to incorrectly defined the "from" setting. User should insert an email address for "from" setting.　 ref:Email With Rails 3 and Heroku Net::SMTPSyntaxError: 501 Syntax error "Sender syntax error" in Chilkat4Xojo This content is subject to limited support.                  

4730
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Can we automatically select previous year based on user input filter value?Knowledge Drop

Last tested: Jan 28, 2021 Yes! I believe there are other way to do, but I hope it can be useful! ( BigQuery StandardSQL)At high level, we want to use sql_always_where parameter with Liquid Parameter to achieve this:STEP 1Add an unquoted type parameter field in your visualization:view: view_name {...parameter: base_year {type: unquoted}} STEP2Add a sql_always_where parameter in your Explore. In a sql_always_where parameter, we want to use Liquid parameter to define BETWEEN condition based on user input (parameter filter value):explore: explore_name {sql_always_where:cast(${view_name.year_from_dimension_group} as int64) -- target fieldbetween({% parameter view_name.base_year %} - 1) -- filter value - 1and {% parameter view_name.base_year %} -- filter value;;}   This content is subject to limited support.                  

340
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Can we hide schema in SQL Runner?Knowledge Drop

Last tested: Jan 20, 2021No, but here's a feature request to +1: https://portal.feedback.us.pendo.io/app/#/case/34511?section=requests This content is subject to limited support.                   

300
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Can we disable curated search by default?Knowledge Drop

Last tested: Feb 11, 2021 Yes! You can disable it from Admin > General > disable curated searchhttps://docs.looker.com/admin-options/settings/general#curated_search This content is subject to limited support.                  

150
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Is there a difference between data actions and "action hub"?Knowledge Drop

Last tested: Feb 12, 2021 In short, the action hub is the server that processes data actions => https://docs.looker.com/admin-options/platform/actionsAction hub is a full framework for managing api keys, integration settings and a few other things. There's an action hub server that sits between Looker and the integration (e.g., twillio). You can additionally set up an action like this: https://docs.looker.com/reference/field-params/action where you are pushing data directly to your integration or some other middleware that you've configured yourself. Action Hub is more secure because you don't have to store your API credentials in plain sight. That said, it is much harder to configure if there's no pre-built configuration. This content is subject to limited support.                  

1400
Kento_Tomine
Community Admin
1 year ago
Kento_Tomine
Community Admin
Kento_TomineCommunity Admin
published in Technical Tips & Tricks

Can we apply pivot over dimension?Knowledge Drop

Last tested: Oct 14, 2020 Yes, we can by using type: list in Measure field.measure: list {type: listlist_field: first_name}dimension: first_name {type: stringsql: ${TABLE}.first_name ;;} *NOTEtype: list creates a list of the distinct values in a given field. If two or more string value are included in the calculation, the data are separated by a comma. Related Community post- Pivot over dimensionOur Doc- Measure Types - list This content is subject to limited support.                  

540
Kento_Tomine
Community Admin
1 year ago
kenelly.almeida
Looker Staff
kenelly.almeidaLooker Staff
published in Technical Tips & Tricks

Default_locale on localization settingsKnowledge Drop

Last tested: Feb 12, 2021 The default_locale is a parameter defined on the localization settings on the manifest file. It is important not to get confused with the instance locale we define on the admin page, even so they work together when se set custom localization.Considering the scenario where we have a en.strings.json file with key-value pairs defined for the dimension id, the localization_settings on the manifest file with default_locale: en, and the instance locale defined as en as well. orders.viewdimension: id {label: "ide"primary_key: yestype: numbersql: ${TABLE}.id ;;}   en.strings.json{"ide": "identifier"} manifest filelocalization_settings: {default_locale: enlocalization_level: permissive} 1) If we assign a locale (pt_BR) to the user on the User settings, and we don't have a string file create yet on that project for that locale. The user could get an error on the explore saying Invalid locale for user "locale_name", not defined in project. No custom localisation would be

2020
kenelly.almeida
Looker Staff
1 year ago
kenelly.almeida
Looker Staff
kenelly.almeidaLooker Staff
published in Technical Tips & Tricks

Does the access_filter get extended when I extend an explore?Knowledge Drop

Last tested: Sep 8, 2020 Yes. We don't need to setup the access filter again, once the access filter from the original explore is also considered on the extended one.Considering the explore Users below, which contains an access filter on the field users.state:explore: users {view_name: usersaccess_filter: {field: users.stateuser_attribute: state_ext} We have are extending this explore as a User Ext explore and not defining the access filter:explore: users_ext {extends: [users]} The state_ext user attribute contains the Alabama value for a specific user, and both explores User and User Ext contains the user-specific data restriction applied. This content is subject to limited support.                  

300
kenelly.almeida
Looker Staff
1 year ago
kenelly.almeida
Looker Staff
kenelly.almeidaLooker Staff
published in Technical Tips & Tricks

Can we hide filters in Looks?Knowledge Drop

Last tested: Aug 16, 2019 Short Answer: No.For custom filters: When you use custom filters in the explore and save it as a Look, we can't edit the custom filter on the Look. Even so the custom filter is not a dynamic filter and the user didn't really need to know what is being filtered. We can't hide the custom filter in Looks, there is a feature request you can vote for on our feedback system: Hide the filter on the look.A workaround is to add the look to a dashboard, and then the custom filter would be automatically hidden.Regular (non-custom) Look filters will also not appear on the dashboard unless the dashboard is edited to add them. This content is subject to limited support.                  

15120
kenelly.almeida
Looker Staff
1 year ago
J
Looker Staff
Judah_HamaouiLooker Staff
published in Technical Tips & Tricks

Modularity: The "Building-Block" Approach to LookML DevelopmentKnowledge Drop

Last tested: May 21, 2018 Awesome video on the concepts for building a clean and scalable Looker model: This content is subject to limited support.                  

250
J
Looker Staff
1 year ago
jon.allen
Looker Staff
jon.allenLooker Staff
published in Technical Tips & Tricks

Tips For Debugging ActionsKnowledge Drop

Last tested: Jul 6, 2020 Get the webhookIDs of the failing action. Map ID with logs on ActionHub Prod* Is it a streaming action?https://docs.looker.com/admin-options/platform/actions#list_of_integrated_services* If yes, do you use of the action use all_results? This will use more than 5000 results and often results in heavier usage. It will also require a connection from ActionHub to Looker.* Is the action “heavy”? (it will execute on a queue)https://github.com/looker/actions/tree/master/src/actions if the action is “heavy” - it will have executeInOwnProcess = true in its definition* When is the action scheduled? useful for knowing if they schedule during peak times Did the action send the data even if the scheduler say it didn’t?  This content is subject to limited support.                  

950
jon.allen
Looker Staff
1 year ago
jon.allen
Looker Staff
jon.allenLooker Staff
published in Technical Tips & Tricks

What happens when a node in a cluster fails?Knowledge Drop

Last tested: Jan 10, 2020 Do the other nodes recognize the failed node?Indirectly, yes -- The node's aren't keeping an eye on each other directly (they aren't pinging each other) but the scheduling engine recognizes the node failure based on the internal db's node heartbeat table and will restart/redistribute the schedule jobs accordingly.When one of the looker node fails, do the active looker nodes start accepting requests that were sent to the failed node?The load balancer will stop sending front-end requests to the failed node when it fails to receive a response. The scheduler will pick up on work that has failed due to a node disappearing, and restart that work on an active node. This content is subject to limited support.                  

270
jon.allen
Looker Staff
1 year ago
jon.allen
Looker Staff
jon.allenLooker Staff
published in Technical Tips & Tricks

Tracking Schedules with System ActivityKnowledge Drop

Last tested: Jan 27, 2020 The scheduled_plan System Activity explore is a great place to follow the movement of a schedule job through the different ​stages of a schedule job's lifecycle.You can access the scheduled_plan System Activity explore with the following link (replace company with your hostname): https://company.looker.com/explore/system__activity/scheduled_planAt a high level, the notable views and fields available in that explore are:Scheduled job Created Date Time & Finalized Date Time ID (scheduled job ID) Status & Status Detail Scheduled plan Content Type ID - shows the content type (Dashboard, Look) as well as the ID of the dashboard or look, plus a link to that content Cron Schedule & Datagroup -- this will tell you when the schedule is *supposed* to run. ID (scheduled plan ID) User ID -- the owner (usually creator) of the schedule plan Scheduled plan destination Address - can be an email address, sftp address, S3 bucket, etc. Format - csv, wysiwyg_p

4060
jon.allen
Looker Staff
1 year ago
jesse.carah
Looker Staff
jesse.carahLooker Staff
published in Technical Tips & Tricks

What git commands are executed under the hood when the "Deploy to Production" button is pressed?Knowledge Drop

Last tested: Oct 15, 2018 You are in dev mode, working on a branch dev-john-smith-wxyz in the local repo in the looker/models-user-123/project-foo directory.When you commit, you commit to the local repo under looker/models-user-123/project-foo and also push your changes to the shared repo - github.com/my-company/project-foo - under the branch dev-john-smith-wxyz.When you deploy to production, Looker first checks that all the commits in github.com/my-company/project-foo branch master are reflected in dev-john-smith-wxyx. If they are not, then you will need to pull changes and merge. Once that is done, you can deploy to production again. Looker will push dev-john-smith-wxyx to the master branch on github.com/my-company/project-foo. When that succeeds it will also then pull master from github.com/my-company/project-foo to the master branch in the local repo in looker/models/project-foo.If you have pull requests set up, instead of deploying to production Looker will create a pull request a

380
jesse.carah
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

How to disable tooltip in chart entirelyKnowledge Drop

Last tested: Apr 13, 2020 This is a feature request you can vote on our feedback system: Turn off tooltip capabilities on a look.Workaround:You are not able to in the measure, use an empty html parameter like so (ex: html: ;; ), because If you use an empty HTML parameter, you will see the IDE error of Must provide HTML for "html"The current workaround for this is to either copy a whitespace character in to the html parameter from a site you can find on line , or to use an empty p tag (<p></p>).This will cause blanks in the data results tab where the values are, as well as in the tooltip when you hover over the values. The underlying data will not change, and the visualization should look the same. This content is subject to limited support.                  

4580
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

What happens under the hood when you hit "reset connection" in Project Settings?Knowledge Drop

Last tested: Apr 7, 2020 When a user hits 'Reset Connection’ in Project Settings, Looker executes a function called revert_repo() calling two jgit commands, reset and clean:resetCommandBy default, reset simply changes the `HEAD` the current branch is pointing at. The `HEAD` reference is found with a call that returns the reference id of the most recent commit to master.When Looker performs the reset, it uses the parameter `--HARD` to reset both the HEAD and the index of the current working directory. You will recall that the index defines where different commits are on the git tree. Therefore, this reset --HARD essentially updates all references in the git tree, ensuring the map is accurateSource: https://git-scm.com/book/en/v2/Git-Tools-Reset-DemystifiedcleanClean removes all untracked files. In the context of git reset, this means that after the tree has been updated, all files not appearing on the tree will be removed.Why does this `magically` solve git problems?If resetting the con

1310
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Filter If / Conditionally Filter / How can I filter only if a user doesn't use a filter?Knowledge Drop

Last tested: Sep 27, 2019 You can do this by using the `conditionally_filter' LookML parameter .This would be an explore level parameter (you would put it in the model file, in the definition for a given explore, and it would apply to that explore and all tiles or looks that use that explore as a base). Syntax looks like:# this is the explore the # conditional_filter param applies toexplore: explore_name {# declare a conditional filterconditionally_filter: {# you can specify multiple filters, by adding new filters:{} blocksfilters: {# the field to conditionally filter onfield: field_name# the default value to filter forvalue: "Looker filter expression"}# unless holds a comma delimited lis# of fields (dimensions or measures) # that a user can filter on to avoid # the conditional filter. unless: [field_name, field_name, …]}} One important note is that this filter will apply even on dashboard tiles and Looks, however the end user will not see the filter generated by the conditional filter

2200
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

ERROR: within group ORDER BY clauses for aggregate functions must be the same -- RedshiftKnowledge Drop

Last tested: May 6, 2020 You are probably either using multiple MEDIAN() functions working on different columns together, or are using multiple other sort based aggregate functions together.Per Redshift documentation:If a statement includes multiple calls to sort-based aggregate functions (LISTAGG, PERCENTILE_CONT, or MEDIAN), they must all use the same ORDER BY values. Note that MEDIAN applies an implicit order by on the expression value. So, if you have two measures like so:measure: bad1 {type: mediansql: column_1 ;;} measure: bad2 {type: mediansql: column_2 ;;}You'll receive the ERROR: within group by... error.To work around this:Do the MEDIAN() in a CTE Do the MEDIAN() in a subquery Only use one MEDIAN at a time Use PERCENTILE_CONT() window function in a measure type: number. Note: this approach is NOT recommended, see this post for the caveats and reasons why.Source, see 'Usage Notes' here: https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html This content is subject to lim

43050
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

What are the collection_id, palette_id, and custom_colors parameters in a LookML dashboard?Knowledge Drop

Last tested: Feb 13, 2020 You may be puzzled to see something like this on a LookML dashboard:https://docs.looker.com/exploring-data/visualizing-query-results/color-collections#color_collection_idsEssentially, in order to get your ids for your custom theme, you'll need to hit the /themes endpoint in the API.https://docs.looker.com/reference/api-and-integration/api-reference/v3.1/theme#get_all_themes This content is subject to limited support.                   

1630
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

No Results, Why doesn't my html render?Knowledge Drop

Last tested: Feb 5, 2020 Let's say you have this really nice html button that you coded up:​​But instead of seeing the button you see this, or "No Results":​​Under the hood, the html only will render if there are results in the returned table, even if you aren't using those results anywhere in the html. To check this, comment out the html and rerun the query. If no results are returned, then that is the issue.​ This content is subject to limited support.                  

890
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Troubleshooting PDT Explore queries get sent straight to 'enqueued'Knowledge Drop

Last tested: Sep 28, 2020 The Problem:Users may report difficulty getting a PDT to build, specifically that a PDT query will go straight to queue, even though few other queries are running and the PDT is not the child of another PDT that is still building.You'll want to verify that this is the scenario you are in by doing the following:Navigate to the Production SQL of the derived table, and see if there is any use of .SQL_TABLE_NAME . If so, this PDT is possibly a dependency of a PDT that is still building. Navigate to Admin/Queries and in another tab open that connection's settings; verify that running queries are fewer than the connection limit and PDT queries are fewer than the Max PDT Connections setting With the Admin/Queries page open, navigate to an explore that utilizes the PDT in question (ideally, only uses that PDT), hit the gear icon and select Rebuild Derived Tables and Re-Run . Tab back to Admin/Queries -- is the query from 2 marked as enqueued even though no or few oth

640
jamesnestler
Looker Staff
1 year ago
jamesnestler
Looker Staff
jamesnestlerLooker Staff
published in Technical Tips & Tricks

Dynamically display an image in a Dashboard, based on a filterKnowledge Drop

Last tested: Oct 30, 2019 There's a few components to this, but it's easier than you might think! At a high level we will be using Liquid and a bit of front end wizardry.1 - LookML ### we're starting with this field, which references a database column with customersdimension: customer {type: stringsql: ${TABLE}.customer ;;}​dimension: customer_img {type: stringsql: ${TABLE}.customer ;;### each if statement should be evaluating the field, then spitting out an image### replace customer here with the name of your field### replace the values with the values from the database column### insert elsif statements for more imageshtml:{% if customer._value == "customer1" %}<img src="https://www.mydomain.com/images/myimage1" />{% elsif customer._value == "customer2" %}<img src="https://www.mydomain.com/images/myimage2" />{% else %}<img src="https://www.mydomain.com/images/myimage1" />{% endif %} ;;} 2 - In your Explore​Navigate to your explore and do the following:Filter on custo

5370
jamesnestler
Looker Staff
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