Question

Is it possible to conditionally set the value of a column using the presence of a join via Liquid?

  • 10 January 2018
  • 1 reply
  • 358 views

I currently have the following code:


  dimension_group: termination_local {
type: time
view_label: "Local Times"
timeframes: [
raw,
time,
hour_of_day,
date,
day_of_week,
week,
month,
quarter,
year,
week_of_year,
month_num,
fiscal_month_num,
day_of_month
]
sql: {% if guarantee_payments.total_guarantee_payments._in_query %}
COALESCE(${TABLE}.termination_time_local, ${guarantee_payments.slot_date})
{% else %}
${TABLE}.termination_time_local
{% endif %}
;;

}


That is, if I am including a column from the guarantee_payments view, then I want to use the coalesce, otherwise just use the normal column.


This works, except that it always causes a join on guarantee_payments, even if no columns use it (probably due to how the necessity of a join is computed), leading to simple queries including the expensive join unnecessarily:


SELECT 
DATE(
orders.termination_time_local

) AS "orders.termination_local_date",

orders.city_code
AS "orders.city_code",
CASE WHEN false THEN 'Yes' ELSE 'No' END
AS "orders.testdim",
COUNT(DISTINCT CASE WHEN (orders.final_status = 'DeliveredStatus') AND (orders.deleted = 0) THEN
orders.id ELSE NULL END) AS "orders.number_of_delivered_orders"
FROM public.orders AS orders
FULL OUTER JOIN public.guarantee_payments AS guarantee_payments ON orders.city_code = guarantee_payments.city
AND DATE(orders.termination_time_local) = (DATE(guarantee_payments.slot_date ))
AND orders.mcd_partner = (guarantee_payments.mcd::BOOLEAN)

WHERE
(((
orders.termination_time_local

) >= ((DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ))) AND (
orders.termination_time_local

) < ((DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ) )))))
GROUP BY 1,2,3
ORDER BY 1 DESC
LIMIT 500

Where no column requires guarantee_payments.


The join is defined as follows (since we want to include guarantee_payments on days without orders):


  join: guarantee_payments {
relationship: one_to_many
type: full_outer
sql_on: orders.city_code = ${guarantee_payments.city}
AND DATE(orders.termination_time_local) = ${guarantee_payments.slot_date}
AND ${orders.mcd_partner} = ${guarantee_payments.mcd};;

}

Is there any way to only do the join when necessary (i.e. when we include a field from guarantee_payments (even indirectly from the orders view)) rather than always adding it?


1 reply

Userlevel 2

Hi @jamesmcm,


Currently, the use of the _in_query liquid parameter will conservatively pull everything that is referenced into a join. Please send us an email at help.looker.com, and we can look into alternative ways to structure this liquid reference for your use case!


Looking forward to hearing from you,


Emma

Reply