Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

NOT operations using Templated Filters

  • 7 April 2017
  • 2 replies
  • 90 views
Niteshd7

I’m trying to use the templated filtering to achieve something like:


IF((hold_date BETWEEN ‘2017-03-29 00:00:00’ AND ‘2017-03-29 23:59:59’) AND (t_stamp NOT BETWEEN ‘2017-03-29 00:00:00’ AND ‘2017-03-29 23:59:59’), 1, 0) AS outside


The templated filter I have used is:


IF(({% condition orders.t_stamp_date %} o.hold_date {% endcondition %}) AND (NOT {% condition orders.t_stamp_date %} o.t_stamp {% endcondition %}), 1, 0) AS outside


and it generates the following sql:


IF((((( o.hold_date ) >= ((DATE(NOW()))) AND ( o.hold_date ) < ((DATE_ADD(DATE(NOW()),INTERVAL 1 day)))))) AND (NOT ((( uo.t_stamp ) >= ((DATE(NOW()))) AND ( uo.t_stamp ) < ((DATE_ADD(DATE(NOW()),INTERVAL 1 day)))))), 1, 0) AS outside


The issue here is that the sql after NOT doesn’t work as expected and thus makes the overall IF block return 0 everytime.

Any suggestions how to implement this correctly?

2 replies

Niteshd7

Found the solution after some “look-ing” 😛


IF((o.hold_date BETWEEN {% date_start orders.t_stamp_date %} AND {% date_end orders.t_stamp_date %}) AND (o.t_stamp NOT BETWEEN {% date_start orders.t_stamp_date %} AND {% date_end orders.t_stamp_date %}), 1, 0) AS outside


generates sql like:


IF((o.hold_date BETWEEN TIMESTAMP(‘2017-04-03’) AND TIMESTAMP(‘2017-04-06’)) AND (o.t_stamp NOT BETWEEN TIMESTAMP(‘2017-04-03’) AND TIMESTAMP(‘2017-04-06’)), 1, 0) AS outside

IanT
Userlevel 7
Badge

I have done similar and was happy apart from when i didnt use that field as a filter and then it generates sql

“WHERE NOT 1=1” if using the condition (although thinking about it now i never tested putting the “NOT” inside the condition block) and if using the date_start/end parameters then it just results in NULL if no filter has been put on the page.

I had to do this:


AND (DT >= {% date_start core_view_d_mobile_deviceapp_date.install_date_date %} OR {% date_start core_view_d_mobile_deviceapp_date.install_date_date %} IS NULL)


enforce the condition unless null is null

Reply


Terms of Service