Wanted to get a list of customers who fit a certain criteria based upon an orderlines table.
The table has a column for every order where the order type is either A or B eg.
OrderId | CustomerId | Type |
---|---|---|
1 | 300 | A |
2 | 300 | B |
3 | 301 | A |
I would like to get a list of customers who have only ever used exclusively Type A -
So in my example I would like to return just CustomerId 301, as they have never made a B order.
Usually I would write something like;
SELECT CustomerId FROM orders WHERE CustomerId NOT IN (
SELECT DISTINCT CustomerId FROM orders WHERE Type = B)
Is there any nifty way to perform this type of logic from within an explore? Or any way other than using a derived table?
(Due to a very crowded explore, I try to discourage the use of new measures that focus on an edge case scenario if it can be avoided)
Solved! Go to Solution.
You could have a couple of measures:
measure: order_typeA_count {
type: count_distinct
filters: [type: “A”]
sql: ${orderID};; }
measure: order_typeB_count {
type: count_distinct
filters: [type: “B”]
sql: ${orderID};; }
add CustomerId as column in your report. Add order_typeA_count > 0 filter and order_typeB_count = 0 filter.
You could have a couple of measures:
measure: order_typeA_count {
type: count_distinct
filters: [type: “A”]
sql: ${orderID};; }
measure: order_typeB_count {
type: count_distinct
filters: [type: “B”]
sql: ${orderID};; }
add CustomerId as column in your report. Add order_typeA_count > 0 filter and order_typeB_count = 0 filter.