Solved: Two views, same fields, can I show all rows in one... - Page 2

CathyM · 11-16-2021 11:49 AM

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.

n_davis

@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

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