Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Looker development

Developing LookML, writing SQL, and modeling data

2570 Topics

O
okkaduco
asked in LookML

Post result of a table as view as it is

Hi, Im exploring Looker for first time, I have a simple requirement to publish query of table as View so that users when access the view can look at data in simple tabluar format. The intention is to basically expose some metadata information of Oracle table as it is to users. I created a view but view practically cannot be accesses without dimensions or views, how can I just display view as it is as tabular data ?

2
O
2 years ago
G
gkatwalMember
asked in LookML

As per best practices, is it advisable to have different files for all views or have them added to a single file?

HI, Does it makes any difference if I have different files for all my views or have them consolidated in one single file?

4
G
2 years ago
moebe
moebeMember
asked in LookML

LoV of Dates, Conditional Filter

Hello everybody I have a customer who extracts data at irregular intervals and stores it in our DWH. Now I am looking for a possibility to show him a LoV of the available days in the reporting. My wish would be: Conditional Filter: latest_etl_rundate Unless: Select different date from LoV Getting the latest date is still easy: select max(etl_valuation_date) from table But how do I get into a LoV of available days? If I format this as a date, no suggestion appears. Unfortunately, if I format this as a string, no suggestion appears either. And of course I don’t want to lose the functionality of a dimensiongroup (drill etc.). Do you have solutions for “my” problem?

9
izzy
2 years ago
Sergiu_Boboc
Sergiu_BobocMember
asked in LookML

Push Changes to Remote Failed

This error I receive after I commit the changes. Also next text appear: refs/heads/dev-sergiu-boboc-mvvv REJECTED_NONFASTFORWARD: How can I solve it?

3
Sergiu_Boboc
2 years ago
vfx
vfxMember
asked in LookML

FLOAT can't be used as dimension in pivot tables (BigQuery)

I came across the need to show a floating point coefficient in the rows and then use it in a calculation to generate a measure. Due to a known limitation of BigQuery - floats can’t be used in PARTITION BY - Looker shows an SQL error. Consider the following view: # Use with Google BigQuery Dielact view: float_dimension { derived_table: { # The following SQL generates 100 random floats and splits them in 2 groups by 'chance' sql: SELECT RAND() as fl, RAND() > 1/ACOS(-1) as chance FROM unnest(generate_array(1, 100, 1)) ;; } dimension: fl { description: "use as row dimension. It will break as soon as you place 'chance' as pivot" type: number sql: ${TABLE}.fl ;; } dimension: chance { description: "use as pivot dimension" type: yesno sql: ${TABLE}.chance ;; } measure: count { description: "put as a measure just for fun :-)" type: count } } Now create an explore and put 'fl' in the rows, 'chance' in the columns. You will get: invali

0
vfx
2 years ago
A
abhineetMember
asked in LookML

Not able to import LookML dashboard with Static Layout into user defined dashboard

I am not able to import LookML dashboard with Static Layout into user defined dashboard. Please suggests hpw we should do that.

0
A
2 years ago
Stephen_Theunissen
Stephen_TheunissenMember
asked in LookML

get number of orders per store per week in buckets

Hello, I want to get the number of orders per store per week in buckets, using dimensions: week, store_id and measure: orders. Currently, 1 row shows the week, the store_id and the number of orders for that week and store. Now I want to count how many stores have less than 50 orders per week, how many have between 50-100, and how many have more than 100 orders per week. For instance, week 1 had 32% of stores with less than 50 orders per week, 40% between 50-100, and 28% > 100. This article suggested to build an NDT to convert the measure of orders to a dimension, after which I can create the buckets. However, my original data is on day level, so I already need to build an NDT to get the measure on week level per store, so that would require me to build an NDT based on an NDT. Is there another way of doing this? Many thanks, Steve

3
Stephen_Theunissen
2 years ago
Dawid
DawidGood Looker
asked in LookML

Field value formatting - square

Even though it’s a specific question, I would also like to have a thread where we can show some interesting and useful patterns for value formats and labels. Right now I have a Area measure that has two options km2 and mi2. First thing is that I’m trying to achieve superscript in value_format to show the value as 1000.34km2 but I believe no HTML or anything is allowed there. Second part is the label. Right now I have Area [km2] and then Area [km2] AVG but it looks clunky to me. What do you guys use in practice?

0
Dawid
2 years ago
simon_onfido
simon_onfidoMember
asked in LookML

Whitespace and liquid conditionals in labels

I’ve been setting up a field’s label/view_label/group_label using Liquid if/else and noticed that Liquid seems to preserve whitespace. This means that if I lay out the label text with newlines, the view and group labels won’t match existing ones and so they are displayed in the field picker twice. Does anyone have a way to avoid this? I’d much prefer to format my code like this: view_label: " {% if _explore._name == 'my_explore' %} Some View Label {% else %} Some Other View Label {% endif %}" rather than this: view_label: "{% if _explore._name == 'my_explore' %}Some View Label{% else %}Some Other View Label{% endif %}"

7
fireplacetv
2 years ago
H
helen.helenMember
asked in LookML

Cannot see LookML dashboards and explores without development mode

Hello everyone, Unfortunately I cannot see any LookML dashboards and explores when I am not in development mode. In dev mode everything works fine but when I click “Exit dev mode” I can see only user-defined dashboards but not LookML ones. I checked permissions and seems that everything is ok, there are just default user permissions for Users and it has permission “see_lookml_dashboards”. But I can see them only in dev mode. At the same time I have on more Looker instance where I can see them and it seems that user permissions there are the same! The code is ok in Github repo for mater branch, so I think that something is wrong with permissions. Maybe you can advise me where I can find permissions settings for users that are out of dev mode? Here is the screenshot for explore without dev mode and there is even no tab “LookML dashboards” in that mode. Thank you very much in advance for the help!

9
Cyril_MTL_Analytics
2 years ago
H
hwimoonMember
asked in LookML

Concat Specific Value to Column Names

Hello, I have a table with a date column as well as a few measures. In all cases, users would only be able to see a single date (it is an As Of Date thing where a user would only see the calculations based on the date). Is there a way I can dynamically concatenate the VALUE of “As Of Date” to the column names (e.g. something like “As Of 4/20/2020 - Month To Date Profit (%)”? I saw in another post that currently concatenating “values” in the “label” parameter isn’t available. Any work-around for this kind of situation?

0
H
2 years ago
sandsp
sandspMember
asked in LookML

Limiting drill from a dimension / measure to some Looks

Hi all, I have a requirement where I need to enable drilldown from a dimensional attribute to a different dashboard. I have used the link method with liquid for the attribute which works as explained in documentation. However there’s a futher requirement to limit the drill-path to certain Looks - i.e. not on every Look that uses the attribute. Is there a way to achieve this or are drill-paths applied globally? Regards, Sandeep

2
sandsp
2 years ago
moebe
moebeMember
asked in LookML

Drill Total, Year, Month, Date

Hi I have the requirement to give the possibility to start the date drill not with the year, but with the total. So customer wants to see, f.e. the total PnL, then he wants to drill to the year, to the month and so on. How can I solve this?

1
Cyril_MTL_Analytics
2 years ago
akhilsai
akhilsaiMember
asked in LookML

View not found error in Looker

Hey All, I have deployed my model into production.The users then can see the dashboards and changes i have made.But,When i create another view and deployed it.It is not visible to the other users .I am able to see when i turn of development mode.What might be the error?

1
Cyril_MTL_Analytics
2 years ago
P
PaulMMember
asked in LookML

set: Not Appearing in View

When I create a set: in a View, it doesn’t show up in the View when I go to Explore it. Is there an admin setting for allowing sets that I am not seeing?

1
ben.krock
2 years ago
G
GintoMember
asked in LookML

date dimension comparison with date range

Hi I’m using a derived table view with this conditional clause below. How do you translate this from snowflake tol lookml? Please advise where MSG_TIMESTAMP >= ‘&RPT_DAY_SF’::timestamp - interval ‘&DAYS_BACK days’ + interval ‘&CUTOFF_HOUR hours’ AND MSG_TIMESTAMP < ‘&RPT_DAY_SF’::timestamp + interval ‘&CUTOFF_HOUR hours’ &RPT_DAY_SF --> today’s date &DAYS_BACK --> number of days e.g. 1 &CUTOFF_HOUR --> number of hours e.g 5

6
G
2 years ago
Shawn_Crenshaw
Shawn_CrenshawMember
asked in LookML

Filtering of schemas

I have a use case where we have multiple schemas in a connection. Each schema contains views that are the same. I have created a user attribute that has the value of one of the schemas, however I need to make the schemas dynamic so a user can select a schema. Here is what I have: view: page_sessions { sql_table_name: {{ _user_attributes[‘custom_schema’]}}.page_sessions_view ;; Right now the user attribute ‘custom_schema’ has one value. I want to enable filtering so that a user can select whatever schema that they choose.

4
Shawn_Crenshaw
2 years ago
P
PaulMMember
asked in LookML

Duration dimension_group - Unexpected Results

Is there a way to force the grain when using Duration? When using only a Duration of Days, Looker still DATEDIFFs by the time. I think generally, when people are expecting the lowest grain to be the date, they would expect this: SELECT TIMESTAMPDIFF(DAY, CAST('2017-10-18 19:43:29' AS TIMESTAMP_NTZ) , CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ) = 1 Not this (which is what Looker does): SELECT (TIMESTAMPDIFF(DAY, CAST('2017-10-18 19:43:29' AS TIMESTAMP_NTZ) , CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ) + CASE WHEN TIMESTAMPDIFF(SECOND, TO_DATE(CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ), CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ) = TIMESTAMPDIFF(SECOND, TO_DATE(CAST('2017-10-18 19:43:29' AS TIMESTAMP_NTZ) ), CAST('2017-10-18 19:43:29' AS TIMESTAMP_NTZ) ) THEN 0 WHEN TIMESTAMPDIFF(SECOND, TO_DATE(CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ), CAST('2017-10-19 14:08:35' AS TIMESTAMP_NTZ) ) < TIMESTAMPDIFF(SECOND, TO_DATE(CAST('2017-10-18 19:43:29' AS TIMESTAMP_NTZ) ), CAS

0
P
2 years ago
purthaler
purthaler
asked in LookML

Make use of filter operator (is equal to, contains, etc.) within Liquid statement

Hi Liquid variables are quite powerful and really useful. Is there and way to get the filter operator (is equal to, contains, does not contain, matches, etc.), which was selected for a filter, as a e.g. Liquid variable and use it e.g. in an IF statement ? cheers

0
purthaler
2 years ago
Jess_R
Jess_R
asked in LookML

DATEDIFF vs duration calculation rounding differences

Found a rounding difference in duration calculation and DATEDIFF. Went about formatting them in different ways to see if they could generate the same results but the rounding difference was consistent. dimension_group: shipping_days_v1 { type: duration sql_start: ${shipped_date::date};; sql_end: ${delivered_date::date} ;; intervals: [day] } dimension: shipping_days_v2 { type: number sql: DATEDIFF(d, ${shipped_raw}, ${delivered_raw} ) ;; } Shipped day: 2018-11-04 16:00:00 Delivered: 2018-11-07 16:00:00 The results for the shipping days v1 will be 2 days while v2 will be 3 days. The same results occur with the hour changes as well (ie. Shipped time 2018-11-03 17:00:00 and delivered time 2018-11-06 16:00:00, v1 results in 2 days, v2 results in 3 )

3
ben.krock
2 years ago
Joshua_Moskovitz
Looker Staff
Joshua_MoskovitzLooker Staff
asked in SQL

Using Postgres and JSON Functions and Operators to parse JSON fields

Any SQL functionality that is native to your dialect can be leveraged in the sql: parameters within LookML, or as derived tables. In this case we will cover Postgres’ JSON functions and operators The ->, ->>, and json_object_keys functions/operators in PostgreSQL allow you to extract values associated with keys in a JSON string. If we have this JSON string: { "text":"I need a gift for my husband's birthday. He loves nice electronics and is in the process of upgrading his wardrobe. I think something special for his wardrobe would be great, a sportscoat or some shoes. I am a little stumped on something that would really surprise him or stand out and could use your help! Sizes:\nShirts-15.5/37\nPant-36x36\nSportscoat-46L\nThank you!", "notes":{ "-JggWN6uFGBViAGDN0_i":{ "user":"simplelogin:72", "message":"Let us know what you think of these initial picks for husband's birthday!", "timestamp":1422381122078 }, "-JhBz_UgDxs

4
J
2 years ago
Dawid
DawidGood Looker
asked in SQL

PostgreSQL Timesamp Error

In relation to my other post Date filter up to current point in time I have created simple CURRENT_TIMESTAMP dimension and gave it some timeframes. When I use YEAR the SQL behind it writes: DATE_PART('year', <field>) but that will not be valid for timestamp, should be using EXTRACT(YEAR FROM <field>) instead, don’t you think? Otherwise following error will be returned: Invalid operation: function pg_catalog.pgdate_part("unknown", timestamp with time zone) does not exist; same happens with month and hour of day . I believe there needs to be a bit more thought into when to choose DATE_PART and whenEXTRACT This could be specific to Redshift (still looking for more information) but that’s why EXTRACT is usually safer option I solved it by casting CURRENT_TIMESTAMP::TIMESTAMP explicitly thought I think it’s bonkers

4
izzy
2 years ago
K
kuopazMember
asked in LookML

Metadata and explores

Hi I’m after a way to list all the fields - dimensions and measures - exposed (not hidden) in all explores in a model. I’ve seen i__looker, but want it for ‘explore’ rather than ‘look’ as below. https://instance_name.looker.com//explore/i__looker/look Or do I have to create a data dictionary? Thanks

1
izzy
2 years ago
Tim_Ingling
Tim_InglingMember
asked in LookML

MEASURES AND DATE FILTERING

I have a formula where I have to first find the max and min dates within a month against the data, then use those values to summarize dollars figures. I have measures that return the max and min dates. Works great. Within a subsequent measure, use those measures as follows: measure: cei_ending_month_current{ label: “CEI Ending Monthly Current Total” type: sum value_format: “#.00” sql: CASE WHEN ${date_date} = ${max_snapshot_update} THEN COALESCE(${agingbalancecurrent},0) ELSE 0 END;; I’ve used a measure within a measure, but that was a simple number type. Aggregation is needed

0
Tim_Ingling
2 years ago
Joao_Rainha
Joao_Rainha
asked in LookML

Merge Query

merged query shows different values after filtering.

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