_TABLE_SUFFIX between

[Seungwoo_Jeong]

Hi, I would like to choose specific start_date and end_date by a filter as where condition.

SQL I used - {CALENDAR.START}, and {CALENDAR.END} are selected by users format is like ‘2020-01-01’

FROM `prod.cv*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_ADD(TIMESTAMP({CALENDAR.START}), INTERVAL -7 DAY)) AND FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_ADD(TIMESTAMP({CALENDAR.END}), INTERVAL +1 DAY))

How could I transfer this to looker?

I would like to use the filter like this

and this view, don’t want to use select * since the table is too huge to get all the columns

view: cv {
sql_table_name: focal-elf-631.prod.cv* ;;

[fabio1]

Hi!

This is typically done with date_start and date_end liquid tags. https://docs.looker.com/reference/liquid-variables

Here’s one way you could write it:

view: my_data {
  filter: date_filter {
    hidden: yes
    type: date
    datatype: datetime # Or your datatype. For writing the correct condition on date_column below
    sql:  
      ${TABLE}._TABLE_SUFFIX 
        BETWEEN {%date_start date_filter %}
        AND {% date_end date_filter %}
      AND {% condition date_filter %} ${TABLE}.date_column {% endcondition %}  ;;
  }
}
explore: my_data {
  always_filter: {
    filters: {
      field: my_data.date_filter
      value: "7 days" #This is the default filter, can be overriden
    }
  }
}

[Seungwoo_Jeong]

Hi, it is really helpful! By the way if I wanna join this table with others and use same date range. How could I do?

[fabio1]

Hi Seungwoo_Jeong,

I would suggest separating the date_filter into two parts:
(1) a filter inside of a field-only view to represent the filter UI
(2) a SQL where clause in a subquery inside of each explore>join>sql, to let you filter the tables at join time.

For more information on putting date fields in a field-only view, take a look at the “Sharing is caring” section in this article:

Only measuring the "latest" values Modeling

What’s in a measure? All too often, we limit ourselves needlessly when we equate measures, metrics, or aggregates with the few basic aggregates that SQL provides out of the box: SUM, COUNT, AVERAGE, maybe MEDIAN if we’re feeling creative and our dialect supports it. For a moment, though, let’s step back and think about what measures are essentially for - taking a potentially large number of underlying data points, usually within a group/dimension, and condensing them down into a digestible datu…

On part 2, here’s an example:

join: foo_join {
    sql_table_name: (
      SELECT * FROM ${foo_view.SQL_TABLE_NAME}
      WHERE _TABLE_SUFFIX BETWEEN {% date_start date_view.date_filter %}
        AND {% date_end date_view.date_filter %}
    );;
    sql_on: ... ;
}