Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Looker development

Developing LookML, writing SQL, and modeling data

2560 Topics

craig.juergens
craig.juergensNew Member
asked in LookML

How-to: Reading only the latest record in a time period using 'deltas only' ETL

How-to: Reporting on end-of-period records only using ‘deltas only’ ETL Originally authored for Looker 6.20.19 Business Requirement: Business users have requested the ability to show only the ‘end of period’ value for inventory. Looker does not yet fully support ‘ending value only’ reporting as an out-of-the-box feature so a custom code solution is required. Example: Users would like to enter the Inventory explore, select a single day in the reporting interface, and have Looker retreive the latest value for each unique combination of location and UPC. Users routinely report on inventory in the past few days/weeks so a hard-coded ‘yesterday’ or ‘now’ view is not an acceptable solution. Technical Pre-Requisites/Assumptions: This write up is intended for a technical audience. The write up assumes a strong understanding of LookML as well as write privileges for your instance. All SQL is written for BigQuery. This approach is built for a database in which a single table stores all inventory

1
eugene.lee
Looker Staff
3 years ago
Gregoire_Niclause
Gregoire_Niclause
asked in LookML

Conditional sum

Hello everyone, I’m trying to make a conditional sum in order to compute the percentage of quota reached. This is my view : salesname | month | amount |monthly quota ----------------+--------------+--------+-------- Jack | Oct | 10 | 100 Jack | Oct | 25 | 100 Jack | Nov | 45 | 200 Bruce | Oct | 60 | 150 Bruce | Nov | 60 | 300 I want to create a measure that would find the percentage quota reached like that : Oct | Nov Jack 35% | 22,5% Bruce 40% | 20% And if I don’t pivot by month : Jack 26,7% Bruce 26,7% So, I think I need to make a conditional sum of the quota for the different month and salesname. I’ve no clue how to undertake that… Can someone help me ? Thank you very much in advance. Grégoire

2
Gregoire_Niclause
3 years ago
J
JasonL
asked in LookML

Required_access_grants

Hello, I’m hoping to get some help with Lookml syntax regarding required_access_grants. Using the example from the Looker documentation (https://docs.looker.com/reference/model-params/access_grant#definition) The following example applies two different access grants so that only users that have either the value "finance" or "executive" assigned to their department user attribute and have the value "yes" assigned to their view_payroll user attribute can access the view. I’m hoping to get syntax advice on how to alter the required_access_grants piece so belonging to only one of the groups would allow access to the view instead of both groups. Any help you can offer will be much appreciated! Cheers!! access_grant: can_view_financial_data { user_attribute: department allowed_values: [ “finance”, “executive” ] } access_grant: can_view_payroll_data { user_attribute: view_payroll allowed_values: [ “yes” ] } view: payroll { … required_access_grants: [can_view_financial_data, can_v

2
B
3 years ago
H
Hisham_ElhadidiMember
asked in LookML

How can I Join Views from multiple models

Hi dears, I hope i can find a solution or even a work-around to my problem, as I’ve already searched the community for similar problems and unfortunately I’ve found no valid answer till end of 2018. Problem: I’ve created 4 models with 3 different connection to 3 different Databases [The main reason for creating 3 different data bases is security as different person handle each database and so we are limiting access to other DBs] Models: Connections: Explores are working fine if I queried data only from the same Model , But when I try to join data from different Models together I get the following error message: After searching the community and the documentations, I have discovered the the error message appears because I query data from multiple connections and the model can only use one connection. My Question: I believe that getting data from multiple sources in a very normal practice, So how can Looker join these Data sources together while insisting on not talking to multiple c

2
H
3 years ago
S
sri75Good Looker
asked in LookML

Filters in Looks

Hi, Is there a way in which i can i can set the value of a filter based on another filter Lets Say i have filter A,B,C,D and now if A =1 then I want B set to be not null and leave the other 2 filters and when A = 2 Then it should set C as not null and leave other 2 filters blank etc Is there a way to do it if so , can you please explain how i can do it

0
S
3 years ago
J
johnbfl2004
asked in SQL

Error when running a Look: "There was a problem running the query"

I promoted a change which created a new view and updated the model. I also created a new look to report items from the model which was working in this environment. I made a change to the view and re-promoted it throughout the environments. I checked it in DVLP mode and it worked. After that, I committed and deployed to production. I exited out of DVLP mode and browsed the look that I had created and that is when I got the above error. This error is so generic, I am not sure where to begin looking. 2 NOTES: When I run the look, I have grabbed the SQL and run it successfully in SQL Runner. The other thing to note is it has caused all other looks in Model to top running which is not good. Any help would be great.

0
J
3 years ago
kpmartin87
kpmartin87Member
asked in LookML

Markdown navigation not compatible with IDE folders

Noticed that the navigation will not work with the new folder set up in IDE (https://docs.looker.com/data-modeling/getting-started/ide-folders, https://docs.looker.com/data-modeling/getting-started/other-project-files). Even when its in the same folder is uses “%2F” instead of “/” in the url which causes an error

8
kpmartin87
3 years ago
S
sri75Good Looker
asked in LookML

LOOKML Doubt

Hi, I am pretty new to Lookml and I have no idea how to do this so need some help When i use a filter on my look I only need it to show 6 months of weeks of data. Lets say i input my filter to and enter as 1 then it should show this week and 5 week prior. lets say i input 0 then it should show the last week and 5 weeks before that and if I input -1 it should show be data of 2 weeks earlier and all 5 weeks before that. How can i do that in LOOKML .

4
mikhailxu
Looker Staff
3 years ago
amc32
amc32New Member
asked in LookML

Unable to drag a file into a folder

I just created a new view and am trying to drag it to my existing views folder but i’m struggling. I have a feeling that a bug may have been introduced along with other changes to the file structure IDE that seemed to have changed this morning. If i try to do it while the folder is collapsed, the interface doesn’t let me drag it onto the folder. But when i expand the folder, the view disappears from the file structure IDE.

0
amc32
3 years ago
H
hbrandaoMember
asked in LookML

Type Conversion on lkml file

Hello guys, I am new to Looker and I am struggling having a field appear as a measure in my Explore. So, I have a field on impala that is originally a string and I am trying to bring as a measure to looker. I create the measure: measure: profile_completion_score_value { label: "Profile Quality Score" description: "The score given to the profile" type: average sql: cast(${TABLE}.profile_completion_score_value as int);; } I also tried bringin it as a dimension, and creating a measure from the dimension, and nothing seems to work. Can someone help me on that?

4
H
3 years ago
rsingh
rsinghMember
asked in LookML

Primary key change by filter

Hello Looker Team I have one table called “end of day balances” which contain balances of each day for each account. so every account will have one record in the table at any particular day and table have records since 2010. There is no unique id of each records so i concat accountid and date column as a compound primary key now i have to join this table with “collection cases” table. Collection cases table have cases open for the accounts which are not paying the balance. So i will do the join on accountid (from balances table) = account__c( from collection table) and collection_table_date=‘today’ If you notice in my join clause, i am joining on non primary key (accountid) but if i choose filter date “today” then logically my accountid is the primary key, but i haven’t defined it in my model anywhere. How looker will know that accountid is the primary key of balances table when date is “1 day” Can you please advise me if i am doing the right way? Thank you in advance Raman

2
rsingh
3 years ago
rsingh
rsinghMember
asked in LookML

Symmetric aggregation value changes when i change my relationship (from many to one -- to one to many)

Hello Looker Team I have read that Looker support symmetric aggregation even if results are fanning out and does not matter what type of join or relationship it is Looker Documentation symmetric_aggregates LookML parameter: Specify whether symmetric aggregates apply in an Explore. so i have 2 tables (collection cases) and (today balance) which i am joining with accountid. Generally there can be many collection cases open per account at a time, so relationship is many to one now I would like to find out total balance of accounts which have collection cases open. In the result set, i will have duplicate balances for same account but i like to count it as one. Looker is producing correct results (sum of distinct account), but when i change the relationship (to test) to many to one, it adds up all the balances (with duplicates) and does not perform symmetric aggregiation. Can you kindly advise why it is happening. ? Thank you Raman

1
davisclark
3 years ago
Shawn_Curry
Shawn_Curry
asked in SQL

SQL Syntax Error on PDT Create: ORA-00907: missing right parenthesis

I am getting the following error for a PDT I created. It is confusing me because I am using a sql_trigger that works for multiple PDTs I have created previously. Any suggestions as to were a missing right Parenthesis could be? Both SQL queries run successfully in SQL developer. derived_table aggregated_fulfillment_ty creation failed: SQL Error in CREATE TABLE: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis derived_table: { sql: Select distinct f.fiscal_year, f.fiscal_day, olf.actual_ship_date_key as ship_date_key, s.ifmx_store_nbr as store_nbr, sum(olf.product_amt - olf.discount_amt) as revenue_amt, sum(olf.order_qty) as Units, sum(olf.product_amt - olf.discount_amt - olf.avg_cost_amt) as Gross_Margin_amt from finldm.order_line_fct olf join finldm.store_dm s on olf.FULFILL_STORE_KEY = s.STORE_KEY join FINLDM.FISCAL_CALENDAR_DM f on olf.actual_ship_date_key = f.date_key join finldm.status_dm s on olf.status_key = s.status_key where f.fiscal_year

3
zckymc
3 years ago
cristian
cristianMember
asked in LookML

Extract Year from date field

Hi All! I have a simple dimension with a date field. I want to create another column with the year from this date field but it doesn’t work so I’m obviously doing something wrong. Any suggestions? dimension: date_field{ type: date sql:${TABLE}.date ;; } dimension: period { type: number sql: EXTRACT(YEARS from ${date_field});; }

5
Dawid
3 years ago
rachel.johnson
Looker Staff
rachel.johnsonLooker Staff
asked in LookML

Creating Dynamic Tiers

The content of this article has been updated and migrated to a Looker Help Center article. But, the information in the comments is useful, so the article will remain here for discussion. For the actual contents, please visit the Help Center.

13
white
Looker Staff
3 years ago
rsingh
rsinghMember
asked in LookML

Running total column specified to "Date" asc only

Hello Looker Team I have a transaction table where i have balances each day . I would like to know how much the total balance recovered on 1oct --> as per my table 1, it shows $10, but i switch the date by desc order, then the running total on 1 oct will be $ 70. can you kindly advise me how can i lock my running total column to date order? Thank you Raman

0
rsingh
3 years ago
P
Prapti
asked in LookML

Required_access_grants in multiple extended views

Hello, is there a way for two Lookml views that have been extended by a single Lookml view to have two separate user attributes in the required_access_grants? In this case, the parent file that extends the two files has no required_access_grants and its job is only to combine the two view files. For example, I need regular users to access the fields defined in the first extended view and only admin users to access the fields defined in the second extended view. Is there a way to do this by defining required_access_grants on a view level? Thanks.

3
B
3 years ago
S
Sriharsha_Akkineni
asked in LookML

Using API call to pull Pivot Query

Hi, I am not sure if it is asked in the right place , but instead of pulling the original query , is there a way i can pull the Pivot query generated in looker using an API Call.

0
S
3 years ago
rsingh
rsinghMember
asked in LookML

How to create dynamic date filter for user

Hello Team I have a transactions table with account id, status, and balance. for each day. and i would like to perform aggregation on that table to find out --> min(balance), max(balance) group by account and status. The subset of data will be depend upon the date user select eg: if user selects 10 oct, it means he like to see recovery amount (opening balance - closing balance) that we have collected till 10 oct. so the sql query would be like select cs.accountid, cs.status, min(cs.principal_outstanding) as o, max(cs.principal_outstanding) as c, (o-c) as recovery_amount, (o-c)/nullif(o,0) as recover_rate from customer_statistics cs where cs.date<=‘2018-01-10’ group by 1,2 User can select date as he likes so i would like to perform aggregation on the data which is equal to and less then the date user select Can you kindly suggest me how can i do it? Thank you Raman

3
rsingh
3 years ago
S
Sriharsha_Akkineni
asked in LookML

Refresh Looks with Other looks

Hi, I am looking to refresh my looks based on the filters I used in another looks. Let say I change my Quarter from Q1 to Q2 I would like the other look to automatically refresh the query and pop up the results before the user checks the other look. Is it possible. I checked if a drill link can help but i couldnt come up with a solution. Is there any other way to do it.

0
S
3 years ago
veronica.phillips
Community Admin
veronica.phillipsCommunity Admin
asked in LookML

Change in JSON Render Formatting (6.22+)

In Looker 6.22, Looker’s method for rendering values in some JSON formats for sending, scheduling, and downloading has changed. This change applies to deliveries and downloads through the Looker UI and the Looker API. Pre-Looker 6.22 Looker uses a dimension or measure’s field name as its rendered value when sending, scheduling, or downloading in all JSON formats from the Looker UI Scheduler and the Looker API. The field names in a data visualization are displayed as they appear in the data table. iFor example: {"view.field_name":"rendered_value"} If you select Apply visualization options on the Looker Send or Schedule pop-up, any available JSON formats will render in the delivery like so: JSON – Simple: {"rendered_label":"rendered_value"} (uses field name from data table) JSON – Simple, Inline: {"rendered_label":"rendered_value"} (uses field name from data table) JSON – Detailed, Inline: {"view.field_name":"rendered_value"} (uses field name from data table) Looker 6.22 - 7.4 Lo

0
veronica.phillips
Community Admin
3 years ago
L
latha
asked in LookML

Aggregate decimal values

Hi there, I have dimension type: number with decimal values. I had created measure to call this dimension. Now I need to display these values as below mentioned format 0.18 0.08 0.45 0.45 and need to do aggregation (average on top of it). But its not calculating after decimal values, expected result is 1.16 instead resulting 0 which is not the appropriate solution. dimension: YYY{ type: number sql: ${TABLE}.“TABLE”*1.0000 ;; value_format_name: decimal_2 } measure: ZZZ{ type: number sql: AVG(${YYY}) ;; } Kindly provide solution. Thanks in advance.

0
L
3 years ago
D
daniella_looker
asked in LookML

URLs to other dashboards - applying TWO filters when linking through

Hi, The objective of what I want to do is: when a user clicks on a logo for a category, to be ‘transported’ to a dashboard drilling down into further detail which must be filtered to a. that category only and b. the comparison filter I’ve applied (vs last year or vs target). I’ve seen how you can do this for just the one filter, but I can’t get applying it to both to work 😕 ! My LookML below: html: < a href=“https://XX.looker.com/dashboards/YY?Category={{ _filters[‘PDT.category’] | url_encode }}” > my attempt, for this the comparison filter is applied but category filter is not working at all… ! html: < a href=“https://XX/dashboards/YY?Comparison={{_filters[‘PDT.comparison’] | url_encode }}&Category={{ _filters[‘PDT.category’] | url_encode }}” > Google results seem to suggest liquid which I’m not familiar with yet, could anyone advise please?

1
izzy
3 years ago
S
SimpleLeak
asked in LookML

Understanding custom filter

Good afternoon. I do not understand the logic of custom filter. I want to create a filter that will leave me 2 entries. This part: ((diff_days(date(2019,09,25), ${table.datetime_date}) > -1) AND (diff_days(date(2019,09,25), ${table.datetime_date}) < 1)) gives me 2019-09-25. This part: ((diff_days(${table.datetime_date}, date(2019,09,25)) > 364) AND (diff_days(${table.datetime_date}, date(2019,09,25)) < 366) AND (extract_years(${table.datetime_date}) = (extract_years(date(2019,09,25))-1))) gives me 2019-08-25. But this: ((diff_days(date(2019,09,25), ${table.datetime_date}) > -1) AND (diff_days(date(2019,09,25), ${table.datetime_date}) < 1)) OR ((diff_days(${table.datetime_date}, date(2019,09,25)) > 364) AND (diff_days(${table.datetime_date}, date(2019,09,25)) < 366) AND (extract_years(${table.datetime_date}) = (extract_years(date(2019,09,25))-1))) gives me more than 2 entries. And I don’t understand why is it. And for me, equalization does not work. looks like a

1
izzy
3 years ago
Ibby_Syed
Ibby_Syed
asked in LookML

Zendesk + Stitch, updated model?

Hey there! I’ve been hunting for a stitch data + zendesk update based on Stitch’s new schema, as a followup from this thread: [Source Block] Zendesk by Stitch Does anyone know where I might find the new block? Here’s the old one: GitHub pipeline-looker-blocks/zendesk LookML files for a Zendesk block compatible with Pipeline's schema - pipeline-looker-blocks/zendesk

6
Jonathan_Palmer
3 years 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