Explorer for wrong references. Best practice

tumikosha
Participant II

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

70dc8b23-429c-4e14-9a8e-2c4af174b33e.png

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
0 1 91
1 REPLY 1

kuopaz
Participant IV

Not sure, but noticed a couple of things that may influence the counts:

The two id dimensions should be set as primary key.

The join: customer relationship should be many_to_one.

Top Labels in this Space
Top Solution Authors