Partitioned table and filters

done
low_priority
reply

(cristian) #1

Hi All!
I have fact table in BQ that is partitioned and requires a filter. I would like the user to choose two dates (start and end date) in filters and then add these dates to the query:
WHERE registerdate between DATE("2019-02-01") AND DATE("2019-02-03")

I have tried this with a sql_always_where: in the explore but I dont know the syntax. Should it be: sql_always_where: registerdate between DATE({% FILTER_NAME %}) AND DATE({% FILTER2_NAME %})

Where should I create the filters?

Any suggestions to guide me through this are appreciated :smiley:


(Andrew Powell) #2

Hi,

I would recommend instead of having a start_date and an end_date, you instead use a filter type field which is a date-type filter:

Create a filter only field in the view file

filter: FILTER_NAME {
    type: date
}

And then use that in the sql_always_where of your model like so:

sql_always_where: {% condition view_name.FILTERNAME %} ${view_name.registerdate} {% endcondition %} ;;

I would recommend having an always_filter in your view to make sure the user has to filter based on the partition field:

  always_filter: {
    filters: {
      field: registerdate
      value: "last 30 days"
    }

If you want to proceed as you are with a start_date and end_date, then you can do this by creating two parameters with type date in your view, and then filtering on those:

parameter: start_date {
    type: date
}

parameter: end_date {
    type: date
}

with

sql_always_where: ${view_name.registerdate} between {% parameter view_name.start_date %} and  {% parameter view_name.end_date %} 

Again I’d set some always filters

Andy


(cristian) #3

Thanks! Helped a lot!


(cristian) #4

A question. Is it possible to force the filter to be displayed in the filter area when browsing the explore?
The “always filter” will be displayed there but the filter from the view is only displayed within that table on the left. Any suggestions?


(Andrew Powell) #5

Not sure I understand what you mean, sorry!

With always_filter the filter will always appear on the filter panel on the right. It also appears as a ‘filter only field’ on the left hand side (field locker), although you cannot select or deselect as its an always_filter


(cristian) #6

I was unclear. Sorry. Yes, the “always filter” always appears in the filter panel. But, lets say I just do a “filter” in a view. This filter will not appear in the filter panel. It only appears in the view on the left hand side. So, is there a way of forcing this filter to appear in the filter area?


(Andrew Powell) #7

Yes thats what the always_filter is used for. I have just noticed a typo in my code above which is probably causing the confusion :). The always filter block should read like this:

  always_filter: {
    filters: {
      field: FILTER_ONLY_FIELD
      value: "last 30 days"
    }

Sorry about that!

If you have the always_filter defined on the registerdate as I had it, then you don’t actually need the filter-only field, or the sql_always_where

Andy