Looker Community

Using a filter only field on the sql_always_where

Hello everyone,

In order to better optimise the resources used to load my tables into Looker, I would like to use a combination of filter and templated_filter into my sql_always_where.

Currently my tables are partitioned by yyyymmdd and I would like to prevent users to load the entire table everytime a query is executed.

explore: test_view {
  sql_always_where: {% condition date_filter %} ymd {% endcondition %} ;;
  always_filter: {
    filters: {
      field: date_filter
      value: "7 days ago"
    }
  }
}

view: test_view {

  sql_table_name: database.test_table ;;
  suggestions: no

### TIMESTAMP DATA ###

  dimension_group: received {
    type: time
    datatype: yyyymmdd
    timeframes: [date, month, week, year]
    sql:${TABLE}.ymd;;
    hidden: yes
  }

  filter: date_filter {
    type: date
    datatype: yyyymmdd
  }

  parameter: timeframe_picker {
    label: "Time Interval"
    type: string
    allowed_value: {value: "Day"}
    allowed_value: {value: "Week"}
    allowed_value: {value: "Month"}
    default_value: "Week"
  }

  dimension: dynamic_timeframe {
    type: string
    label: "Dates"
    sql:
    CASE
    WHEN {% condition timeframe_picker %} 'Day' {% endcondition %} THEN ${received_date}
    WHEN {% condition timeframe_picker %} 'Week' {% endcondition %} THEN ${received_week}
    WHEN {% condition timeframe_picker %} 'Month' {% endcondition %} THEN ${received_month}
    END ;;
  }

### DIMENSIONS ###

  dimension: id {
    primary_key: yes
    type: string
    sql: ${TABLE}.id ;;
  }

  dimension: source {
    type: string
    sql: ${TABLE}.source ;;
  }

    measure: count_event_id {
    group_label: "Measures"
    type: count
    drill_fields: [detail*]
  }

  set: detail {
    fields: [
      ymd.date,
      id,
      source
    ]
  }

}

As of now, I am not able to do it unless I change my structure to a derived_table, and using the templated filters inside my where clause.

Since my model relies on many tables with templated filters, I have issues making sure that the filters are applied to multiple views in the joins.

Any way of solving it in this way above?

Hi Heber,

I don’t think you need sql_always_where (unless I’m missing something). The always_filter will guarentee that there is always a filter set on test_view.received. You can use dimension in {% condition %} so you can say {% condition test_view.received %} if you need to do that in a derived table somewhere.

explore: test_view {
  always_filter: {
    filters: {
      field: received
      value: "7 days ago"
    }
  }
}

view: test_view {
  sql_table_name: database.test_table ;;
  suggestions: no

### TIMESTAMP DATA ###

  dimension_group: received {
    type: time
    datatype: yyyymmdd
    timeframes: [date, month, week, year]
    sql:${TABLE}.ymd;;
    hidden: yes
  }
 ...

Thanks a lot for the tip!