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! Go to Solution.
@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.