Passing Literal Values to Predicate

[Jeff3000]

We have a BigQuery table partitioned on date. In order to take advantage of the partition, we need to pass the literal dates (start and end) into the where clause. Passing something like fact_table.date between calendar.start_date and calendar.end_date won’t work.

Because we have a custom calendar, using a filter of type date also won’t work. “Last Month” doesn’t line up with our last month, and users typically don’t know when our months, quarters, and years start and end.

Is there a way to pass a literal value or string (date in this case). I’m sure this has to be a common problem, but my Google skills are failing me. I’ve tried several different things with parameters, filters, and liquid, but all to no avail.

Open to any and all suggestions. Thank you!

[45228238]

I have the same mentioned problem, just checking if you had found any workaround? Thanks

[robertcarr]

Thanks for the posts both.

If you want to pass a literal value through from a filter string, you could do something along the lines of creating a filter field and then adding a sql_always_where clause to your model. Let me know if this helps or feel free to elaborate on your problem.

 filter: filter_name {
type: string
}

sql_always_where: {% condition filter_name %} view_name.partition_column {% endcondition %}

You should also be able to leverage native filters for querying BQ partitions. There is also date_start and date_end options within liquid (https://cloud.google.com/looker/docs/liquid-variable-reference#usage_of_date_start_and_date_end).

[45228238]

@robertcarr - Thanks for the reply.

Yeah I did the same as mentioned  - (https://cloud.google.com/looker/docs/liquid-variable-reference#usage_of_date_start_and_date_end

I had to slightly change the date functions based on BIQ Query