I have 2 views
Orders.view
derived_table: {
sql:
select 1 as id, 100 as amount, 1 as customer_id
UNION ALL select 2 as id, 200 as amount, 2 as customer_id
UNION ALL select 3 as id, 300 as amount, 3 as customer_id
UNION ALL select 4 as id,300 as amount, 2 as customer_id
UNION ALL select 5 as id,, 300 as amount, 300 as customer_id -- wrong
UNION ALL select 6 as id, 300 as amount, 400 as customer_id -- wrong
;;
}
dimension:id {
type: number
}
measure: count {
type: count
drill_fields: [id,customer_id] # ???
}
Customers.view
derived_table: {
sql:
select 1 as id, "Pedro" as name
UNION ALL select 2 as id, "Sasha" as name
UNION ALL select 3 as id, "Roma" as name
UNION ALL select 4 as id, "Gvido" as name
UNION ALL select 5 as id, "Arnold" as name
;;
}
dimension:id {
type: number
}
measure: count {
type: count
drill_fields: [id,customer_id] # ???
}
2 orders have wrong `customers_id`: [300,400]
explore: orders {
join: customers {
sql_on: ${orders.customer_id} = ${customers.id} ;;
relationship: one_to_many
type: left_outer
}
join: customers_exclude {
from: customers
sql_on: ${orders.customer_id} = ${customers_exclude.id} ;;
# sql_where: ${orders.customer_id} is not NULL;;
sql_where: ${customers_exclude.id} is NULL;;
relationship: one_to_many
type: full_outer
}
}
This produce
How to make explorer to produce the result like this? With possibility to drill corresponding ids?
Total orders Count | Correct orders Count | Incorrect orders count |
---|---|---|
6 | 4 | 2 |