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

CathyM
Participant I

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.

Solved Solved
0 5 1,623
1 ACCEPTED SOLUTION

n_davis
Participant V

@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.

View solution in original post

5 REPLIES 5

n_davis
Participant V

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

CathyM
Participant I

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_davis
Participant V

@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
Participant I

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
Participant I

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.

Top Labels in this Space
Top Solution Authors