date dimension comparison with date range

Ginto
Participant I

Hi I’m using a derived table view with this conditional clause below. How do you translate this from snowflake tol lookml? Please advise

where
MSG_TIMESTAMP >= ‘&RPT_DAY_SF’::timestamp - interval ‘&DAYS_BACK days’ + interval ‘&CUTOFF_HOUR hours’
AND MSG_TIMESTAMP < ‘&RPT_DAY_SF’::timestamp + interval ‘&CUTOFF_HOUR hours’

&RPT_DAY_SF --> today’s date
&DAYS_BACK --> number of days e.g. 1
&CUTOFF_HOUR --> number of hours e.g 5

0 6 460
6 REPLIES 6

Hey Ginto!

Could you share a bit more details on what you’re trying to achieve here? Replacing those variables?

Ginto
Participant I

Hi Cyril,

I was able to code those variable parameters into the above WHERE clause in my derived table view (Snowflake sql) as:

WHERE
MSG_TIMESTAMP >= current_date::timestamp - interval ’ {% parameter days_back %} days’ + interval ’ {% parameter cutoff_hour %} hours ’
– AND MSG_TIMESTAMP < current_date::timestamp + interval ’ {% parameter cutoff_hour %} hours ’

parameter: days_back {
type: number
label: “Transaction Date Back”
}
parameter: cutoff_hour {
type: number
label: “cutoff hours”
}

However if I want to replace the ‘current_date’ in the sql into a parameter named RPT_DAY with a default value of current date, how should I do that? The report query runs daily via the scheduler. I should have the ability to change the value for RPT_DAY if I need to rerun the report query.

Any help is appreciated.

moebe
Participant V

Is your question because of the snowflake result cache?
If yes, you would not need to change anything, because snowflake can handle current_date()

https://docs.snowflake.com/en/user-guide/querying-persisted-results.html

You can use some liquid logic to add a conditional statement:

  [...] where
  {% if VIEW_NAME.rpt_day._in_query %}
    created_at >= {% parameter VIEW_NAME.rpt_day %}::timestamp - INTERVAL '{% parameter VIEW_NAME.days_back %} days' + INTERVAL '{% parameter VIEW_NAME.cutoff_hour %} hours'
    and created_at < {% parameter VIEW_NAME.rpt_day %}::timestamp + INTERVAL '{% parameter VIEW_NAME.cutoff_hour %} hours'
  {% else %}
  created_at >= current_date::timestamp - INTERVAL '{% parameter VIEW_NAME.days_back %} days' + INTERVAL '{% parameter VIEW_NAME.cutoff_hour %} hours'
    AND created_at < current_date::timestamp + INTERVAL '{% parameter VIEW_NAME.cutoff_hour %} hours'
  {% endif %}  

  }
  
parameter: rpt_day {
   label: "Report Day"
   type: date
  }
  
parameter: days_back {
   type: number
   label: "Transaction Date Back"
   default_value: "1"
  }
  
parameter: cutoff_hour {
   type: number
   label: "cutoff hours"
   default_value: "0"
  }

Just replace VIEW_NAME and the default values, that should do the trick!

Ginto
Participant I

@Cyril_MTL_Analy ,

This is perfect! It will work the way we want it for our legacy reports which we are migrating from old reporting tool to Looker using snowflake. So basically we are still learning.

Thank you!

Ginto
Participant I

@moebe Please see Cyril’s response to address my problem. But thank you for your response. Your doc link will be something I can use going forward for my learning curve.

Top Labels in this Space
Top Solution Authors