SQL Exists, or IS IN functionality.

Looker Q&A Looker
1

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)

2

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.

2 Likes