User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Solved

Two views, same fields, can I show all rows in one Explore query?

  • 16 November 2021
  • 5 replies
  • 32 views
CathyM
Userlevel 1

Context

I have two views - `call_speed_of_answer.view` and `call_abandonment_rate.view`. They extend a `call_base.view` which defines all the dimensions and measures used in the two views.

In the `call_speed_of_answer` and `call_abandonment_rate` views, I set the `sql:` parameters for measures from the base view to define the custom logic for each’s calculations of `passing`.

Question

The two call views have the exact same list of dimensions and measures. I want to combine the results back into one single result query, like this:

Customer Measure Target Actual
Customer_A Speed of Answer 75% 82%
Customer_A Abandonment Rate 5% 3%
Customer_B Speed of Answer 80% 79%

 

Right now, I have the two call views as their own Explores. How can I union the results of the two views back into one single view? Are there other LookML options besides a third derived table view that manually UNIONs the results together via SQL? Is there a different way to extend the base view with custom logic for each measure type? We hope to expand this base view to cover other measure types besides calls, so I want to keep this flexible and maintainable.

icon

Best answer by n_davis 16 November 2021, 22:40

View original

5 replies

N

Could you just use one view and filter the measures directly? https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures-

CathyM
Userlevel 1

I’m not sure. The denominator and logic for the result calculations is different between these two - and will only diverge more when we add other measure types for member satisfaction and turnaround times - so I think `extends` is still the right way to keep the code DRY?

But if I did change to one single view with `yesno` dimensions and filters instead, would I use a final measure that coalesces all the different filtered measures into one single measure to recreate the table above?

I was hoping to reduce complexity of all the metrics by using extends, so that each metric type (speed of answer, abandonment rate, member satisfaction, etc) was self-contained but used the same base view for consistency. I planned to do a separate dashboard tile for each metric type, but I have a request where one single result view would be really beneficial.

N

@CathyM  Re-reading this, and I’m not sure why everything isn’t in one table. How is the base view structured? If it is dimension/dimension/measure/measure, then you should just be able to do:

  dimensions: customer {
    type: string
    ${TABLE}."CUSTOMER";;
  }


  dimensions: measure {
    type: string
    ${TABLE}."MEASURE";;
  }


  measure: target {
    type: average
    sql: ${TABLE}."TARGET";;
  }


  measure: actual {
    type: average
    sql: ${TABLE}."ACTUAL";;
  }

 

Then you could select which measure names pull through to the end result by filtering in the explore using sql_always_where or just by filtering the measure name in the viz.

CathyM
Userlevel 1

The data for metric types is in different tables, and there is custom logic for each metric type in the forecasting, risk assessment, and recoverability measures.

But… maybe I can union the data in a base view, and add refinements with the custom logic instead of extends. I’ll do some refactoring and see if I can simplify it that way, thanks!

CathyM
Userlevel 1

Thanks, @n_davis . I refactored the source table to union the metric types into one table and set that as the view source in Looker, and it works fabulously.

Reply