Joins using AND/OR in lookML


(Sarah Ferguson) #1

Hi, I’m trying to join two tables to make an explore view. One contains information on orders and the second on costs by product, type and store. Some of these costs table have a start_date and expiration_date column for where costs have changed. On some occasions these columns are null.

I have tried to write this in lookML, but it only seems to pull through some of the costs not all of them. The issue seems to be with the date clauses. Can someone please advise on what I’ve done wrong?

 join: product_cost {
    relationship: many_to_one
    sql_on: ${product_cost.product_id} = ${product_info.product_id}
            AND ${product_cost.store_id} = ${product_info.store_id}
            AND ${product_cost.type_id} = ${product_info.type_id}
            AND ((${product_cost.start_date} IS NOT NULL AND ${order_info.date_date} >= ${product_cost.start_date}) OR ${product_cost.start_date} IS NULL)
            AND ((${product_cost.expiration_date} IS NOT NULL AND ${order_info.date_date} <= ${product_cost.expiration_date}) OR ${product_cost.expiration_date} IS NULL)
            ;;

(fabio) #2

Your date logic seems fine to me. You do seem to have a typo on your type logic though:

AND ${product_cost.type_id} = ${order_info.store_id}

As an aside - one trick that I like to use to make these kinds of NULL date expressions more readable is to use COALESCE/IFNULL to treat these null dates with a more “logical” meaning:

AND ${order_info.date_date} >= IFNULL(${product_cost.start_date}, '1900-01-01'::date)
AND ${order_info.date_date} <= IFNULL(${product_cost.end_date}, '2500-01-01'::date)

(Sarah Ferguson) #3

Hi Fabio, thanks for spotting the typo. I’ve tried your edits but still have the same issue.

I’ve also just noticed I copied the logic incorrectly. The join references two views which might be casing the issue. the above logic is under an explore on the view ‘order_info’ (see below), and the join on ‘product_cost’ references another join between ‘order_info’ and ‘product_info’.

Does this shed any further light on potential issues?

explore: order_info
    join: product_info {
    relationship: many_to_one
    sql_on: ${order_info.product_info_id} = ${product_info.product_info_id}

(fabio) #4

Hi Sarah. Overall it looks like it should work.

One useful approach to troubleshooting joins is to start from the query that is giving you unexpected results, and select the columns from both sides of the join that are supposed to be equal, so you can see where nulls are being introduced.

Something like

SELECT 
  A.b_id IS NULL,
  B.id IS NULL,
  B.c_id IS NULL,
  C.id IS NULL,
  count(*)
FROM A
JOIN B ON B.id = A.b_id
JOIN C ON C.id = B.c_id
GROUP BY 1,2,3,4