Question

Inline/Isolated table in LookML


Userlevel 1

Hi Team,

 

I want to create a brand new table in LookML Model, with some values also (some 20 rows may be). The table should NOT be as a derived table from any other source table from any databases. How can this be possible? 
In simple way, the table might look like this: 

ID Name Percent
1 James 87%
2 Mary 92%

 

Can someone please help on this? 


This topic has been closed for comments

26 replies

Userlevel 7
Badge +1

Can I ask why can’t it be derived table? Where is it supposed to persist?

If your answer is nowhere then the only other way I can think of including it is a dimension with CASE

that uses a field directly related to your Name. If this table was to be joined to, let’s say, users table, I would create it as a manually-entered dimension that gets value depending on the value of other field.

Userlevel 1

Hi @Dawid_Nawrot ,

 

I didnt say that it can't be Derived Table. I was just trying to say, I don't have any Tables/Views in my database BigQuery from which I can derive as a new table.

The columns in the new required table are to be different and should not be Joined with any other View in my Explore.

Also, I want this new table/view, in my existing Model itself, where I have rest all other Views. I want to bring that to my existing Explore where I have all other Views joined. But I don't want this View to be Joined. This table is going to be a Range/Comparison table, like this below: 

 

Other KPIs have to be compared according to this range Low and High and Percent needs to be used.

 

Low High Percent
$0 $500 9
$501 $1000 10
$1001 $5000 15

 

Userlevel 7
Badge +1

Then I would use the derived table like this:

view: customer_order_facts {
derived_table: {
sql:
SELECT
customer_id,
COUNT(*) AS lifetime_orders,
SUM(total) AS lifetime_spend
FROM
order
GROUP BY
customer_id ;;
}
}

 

Meaning you’d have to keep a SQL in Looker with all the values. We keep those in dictionary tables in our Data Warehouse. 

view: some_view_name {
derived_table: {
sql:
SELECT
0 AS low,
500 AS high,
9 AS percent
UNION ALL
SELECT 501, 1000, 10
UNION ALL
SELECT 1001, 5000, 15
}
}

 

Userlevel 1

@Dawid_Nawrot ,

 

thanks makes sense. this looks simialr what I need.

 

So this new View - I have add as a new View or directly I can add inside the Model inside Explore? 

If I add as a new View, how to make sure, it shows up in my Explore, so I can use?

Userlevel 7
Badge +1

You treat it just as any other standalone view. I did that with some targets we had before moving it to a Data Warehouse layer.

This is what I had:

file: some_targets.view

view: some_targets {
derived_table: {
sql: SELECT 1 UNION SELECT 2 (and so on) ;;
}
}

file: <model_name>.model

In my case it didn’t matter which model I used it in but I chose “core data” or “main”

explore: some_targets {
}

I didn’t need any parameters because then the Explore would naturally take a name of “Some Targets”, which is exactly what I wanted.

Sometimes I wanted to use it in conjunction with a different view, then I just joined:

explore: some_other_explore {
from: orders

join: some_targets {
relationship: one_to_many
}
}

With appropriate parameters for the join

Userlevel 1

Thank you @Dawid_Nawrot . 

This adds some more clarity.  I will carry out as said above and let you know. 
I am new to LookML, so have questions as well. 

 

Also, as I don’t have a common Field in the new view to be Joined, how can I use the new view in the existing Explore (this has already some 8 tables), without using the ‘sql_on’ statement? 

 

I am trying something like this - 

 

explore: My_Explore{
.

.

.

join: some_view_name{     /*this is my new View*/
 type: cross
  relationship: many_to_many
}

Userlevel 7
Badge +1

Use it like this

 


join: some_view_name {
type: cross
relationship: one_to_one
sql: ;;
}

 

Userlevel 1

Thank you @Dawid_Nawrot .

 

The new View is created and visible in my Explore as well, and I can add 3 fields (low, high, percent) in my Data/Visualization also.

However, I am creating one new ‘Measure’ in another existing Database View (Invoice), like this below: 

 

view: invoice {

measure: Rebate {
    label: "Rebate"
    type: number
    sql: CASE WHEN ${Purchases} > high THEN 1 ELSE 0 END ;;  /*’high’ is new field in my new View*/

}

 

Looker is giving me below error: 

Query execution failed: - Unrecognized name: high at [3:238]

 

Any clue here? 

 

Userlevel 7
Badge +1

You need to refer to it as 

${some_new_view.high}

 

Userlevel 1

Thank you @Dawid_Nawrot .

 

I guess while creating the Measure in another View (Invoice), its only allowing all the fields ONLY from that particular View. so the fields from the new View, like low, high, percent don't show up in ${ }.

Similarly, when I try creating a Measure in the new View, in the CASE Statement, ${Purchases} don't show up and low, high, percent are available inside ${ }.

Userlevel 7
Badge +1

They don’t show up because there’s a condition that they will only work (be used) if that view is joined in the explore.

When you’re adding a field from another view Looker doesn’t know if it’s included in an explore because one view could be used in multiple explores but the join exists only in one..

Userlevel 1

Ok @Dawid_Nawrot .

 

I tested this approach. When I use like this - 

measure: Rebate {
    label: "Rebate"
    type: number
    sql: CASE WHEN ${Purchases} > ${rebate_table.high} THEN 1 ELSE 0 END ;;
  }

 

I have the below error , and this is the Query generated - 

Query execution failed: - Unrecognized name: rebate_table at [11:238]

 

WITH rebate_table AS (SELECT        0 AS low,        500 AS high,        9 AS percent      UNION ALL      SELECT 501, 1000, 10      UNION ALL      SELECT 1001, 5000, 15 )SELECT  ……	

 

But, I have joined the new View already in My single Explore as you had suggested above - 

join: rebate_table {
 type: cross
  relationship: one_to_one
  sql:  ;;

}

 

I see the new View in my Explore while adding in Data and Visualization too. Not sure, why the above error comes. 

Userlevel 7
Badge +1

Try to put

include: rebate_table.view.lkml 

at the top of your  main view

Userlevel 1

@Dawid_Nawrot ,

I had already added an Include keyword in my Explore/Model page, like: 

include: "/common_views/**/*.view."     /*common_views in the new folder for new views*/

 

Tried adding also,

include: rebate_table.view.lkml 

include: "/common_views/**/rebate_table.view.lkml"

Somehow still the same error. Looking into further.

Userlevel 7
Badge +1

Have you got any labels applied to the join? is “rebate_table” the name of the join and the view?

Userlevel 1

@Dawid_Nawrot ,

 

There is no ‘view_label: ‘ used though after the join:

 

Below is the one used: 

join: rebate_table {
 type: cross
  relationship: one_to_one
  sql:  ;;

 

Userlevel 7
Badge +1

And what is the name of the view file and the view name inside the rebate file?

Userlevel 1

Here is how the new View is created - 

 

view: rebate_table {
  derived_table: {
    sql:
      SELECT
        0 AS low,
        500 AS high,
        9 AS percent
      UNION ALL
      SELECT 501, 1000, 10
      UNION ALL
      SELECT 1001, 5000, 15 ;;
  }

  dimension: low {
    type: number
    sql: ${TABLE}.low ;;
  }

  dimension: high {
    type: number
    sql: ${TABLE}.high ;;
  }

  dimension: percent {
    type: number
    sql: ${TABLE}.percent ;;
  }
}
 

Userlevel 7
Badge +1

What does your SQL look like when you run the explore query with your new field that uses both views?  

Userlevel 1

@Dawid_Nawrot ,

I do have certain other filters, conditions in Explore which are still working fine (before the usage of the new View). After using the newly created measure: Rebate (from Invoice view) in Explore, my SQL looks like below - 

 

WITH rebate_table AS (SELECT
        0 AS low,
        500 AS high,
        9 AS percent
      UNION ALL
      SELECT 501, 1000, 10
      UNION ALL
      SELECT 1001, 5000, 15 )
SELECT
    CASE WHEN (COALESCE(SUM(CASE WHEN (((CASE WHEN cardinal_account_group_cv.afltn_num in (779,998)  THEN 'Y' ELSE 'N' END) = 'Y')) AND (product_cv.ITEM_TYPE_CDE  IN (1,9,30)) THEN invoice_line_cv.EXT_SELL_DLR  ELSE NULL END), 0)) > rebate_table.low THEN 1 ELSE 0 END  AS invoice_line_cv_rebate_1
FROM `VI0_PHM_SDW_NP.INVOICE_LINE_CV` AS invoice_line_cv
LEFT JOIN `VI0_PHM_SDW_NP.TIME_DETAIL_CV`
     AS time_detail_cv ON invoice_line_cv.DTE_KEY_NUM = time_detail_cv.DTE_KEY_NUM
LEFT JOIN `VI0_PHM_SDW_NP.CONTRACT_GROUP_CV`
     AS contract_group_cv ON invoice_line_cv.CNTRCT_GROUP_KEY_NUM = contract_group_cv.cntrct_group_key_num
LEFT JOIN `VI0_PHM_SDW_NP.CARDINAL_ACCOUNT_GROUP_CV`
     AS cardinal_account_group_cv ON contract_group_cv.card_acct_group_num = cardinal_account_group_cv.card_acct_group_num
LEFT JOIN `VI0_PHM_SDW_NP.PRODUCT_CV`
     AS product_cv ON invoice_line_cv.PROD_KEY_NUM = product_cv.PROD_KEY_NUM


WHERE (((time_detail_cv.RFRNC_DTE ) >= ((DATE(TIMESTAMP_TRUNC(CAST(TIMESTAMP(CONCAT(CAST(DATE_ADD(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS DATE), INTERVAL -1 YEAR) AS STRING), ' ', CAST(TIME(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS TIMESTAMP)) AS STRING))) AS TIMESTAMP), DAY)))) AND (time_detail_cv.RFRNC_DTE ) < ((DATE(TIMESTAMP_TRUNC(CAST(TIMESTAMP(CONCAT(CAST(DATE_ADD(CAST(TIMESTAMP(CONCAT(CAST(DATE_ADD(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS DATE), INTERVAL -1 YEAR) AS STRING), ' ', CAST(TIME(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS TIMESTAMP)) AS STRING))) AS DATE), INTERVAL 2 YEAR) AS STRING), ' ', CAST(TIME(CAST(TIMESTAMP(CONCAT(CAST(DATE_ADD(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS DATE), INTERVAL -1 YEAR) AS STRING), ' ', CAST(TIME(CAST(TIMESTAMP_TRUNC(CAST(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS TIMESTAMP), YEAR) AS TIMESTAMP)) AS STRING))) AS TIMESTAMP)) AS STRING))) AS TIMESTAMP), DAY))))))
LIMIT 500

Userlevel 7
Badge +1

Well, the error makes sense now. The rebate_table does not exist in FROM or any of the JOINs, which means the fields from the CTE are not available to the SELECT statement.

 

Try adding a parameter from in the join like

join: rebate_table {

  from: rebate_table

Userlevel 1

@Dawid_Nawrot , 

The Error and SQL remains the same. 

After adding that in Explore, I dont see a new join getting added in the Query too.

Is it because we don’t have a sql_on: in our Explore join ? 

If I add ‘sql_on:’ instead of ‘sql:’ , I am seeing the rebate_table is getting added in Join in SQL Query, but a ON is missing, and the error is:  

‘Query execution failed: - Syntax error: Unexpected keyword WHERE at [23:1]’

join: rebate_table {

from: rebate_table
 type: cross
  relationship: one_to_one
  sql_on:  ;;

 

Portion of the SQL Query: 

 

LEFT JOIN `VI0_PHM_SDW_NP.PRODUCT_CV`     AS product_cv ON invoice_line_cv.PROD_KEY_NUM = product_cv.PROD_KEY_NUMCROSS JOIN rebate_table ONWHERE (
Userlevel 7
Badge +1

Perhaps because it’s many to many you need to use sql on. I only used CROSS with one row, almost like attaching extra columns.

Try this

 

join: rebate_table {

  sql_on: 1 = 1 ;;

}


Though I still wonder how will you manage many to many relationship here

Userlevel 1

@Dawid_Nawrot ,

With this piece, I have another error:

join: rebate_table {
  from: rebate_table
  type: cross
  relationship: one_to_one
  sql_on: 1=1  ;;

Query execution failed: - ON clause cannot be used with CROSS JOIN at [22:25]

 

With many_to_many relationship, I am having this error: 

Field 'Invoice Line Rebate' depends on 'Invoice Line SOURCE Purchases' which cannot be calculated because of a one_to_many or many_to_many join. Consider adding a primary key to 'invoice_line_cv'

sre_edna_poc_project/views/invoice_line_cv.view.lkml:67

 

Now trying to juggle between other options  :grinning:

Userlevel 7
Badge +1

ahh of course, no `sql_on` for CROSS JOINS.. it’s weird that the join SQL isn’t added just with `sql` parameter. it worked for me before but again, I never had to do CROSS join with a table with more than one row. Try different values of relationship as well