Question

Filter used in PDT is adding condition to outer query. This breaks the query.

  • 18 July 2022
  • 0 replies
  • 24 views

We have a filter that looks like this 

  filter: date_filter {
type: date
sql:date between {% date_start date_filter %} and {% date_end date_filter %};;
}

This filter is used in part of a CTE that looks like this 

     vet as (
select t.veterinarian
-- , r.product_group
, r.rev_category as product_grp
, t.veterinarian as rank_flag
, count(distinct appt_id) as appt_cnt
, sum(revenue) as sum_rev
, sum_rev / appt_cnt as prd_grp_rev_per_appt
from bi.transactions_with_appointments t
left join rev_category r on r.product_group = t.product_group
where r.rev_category != 'Other/Misc'
and {% condition vet_filter %} t.veterinarian {% endcondition %}
and t.date between {% date_start date_filter %} and {% date_end date_filter %}
group by 1, 2, 3
having appt_cnt > 0
)

That all works great. The problem is, when Looker renders the SQL, we end up with the liquid substitution, along with a filter added to the rendered SQL. It looks like this below. Not shown in my code block is the CTE query above it named kpi_report by looker.
 

SELECT
kpi_report.product_grp AS "kpi_report.product_group",
COALESCE(SUM(kpi_report.sum_rev ), 0) AS "kpi_report.total_rev"
FROM kpi_report
WHERE (date between CONVERT_TIMEZONE('America/Chicago', 'UTC', DATEADD(day,-6, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Chicago', GETDATE())) )) and CONVERT_TIMEZONE('America/Chicago', 'UTC', DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Chicago', GETDATE())) ) ))) AND (((kpi_report.veterinarian = 'Valer') or rank_flag in ('Top 3','Bottom 3')) )
GROUP BY
1
ORDER BY
2 DESC
LIMIT 500

Due to the structure of the query, we can’t pull the “date” field out to the top level. The filter would be totally fine, if it just affected the derived table code portion of the query. Is there  a way to have Looker stop adding filters to the outer rendered CTE query? 


0 replies

Be the first to reply!

Reply