Changing SQL tables from View based upon Filtered Date

liquid
parameter
lookml

(Kyle) #1

We currently have two tables one that has a rolling 6 months of data (table_1) and another that has the entire history (table_2). What I am trying to code is that if a user changes a date filter to try and pull data from more than 6 months ago it will pull from table_2 else it will pull from table_1. I am thinking that I need to use a start and end date parameter but I am having some issues. Any thoughts?

Example Code
sql_table_name:
{% if view.date <= ‘rolling 6 month date’ %} table_2
{% else %} table_1
{% endif %} ;;


(Kyle) #2

Any thoughts on this?


(will.adams) #3

Hi Kyle, yeah this should be doable, though it may require more than a single if-else. Since date filters are sometimes unbounded (before yesterday or after 90 days ago) and are sometimes bounded (from 2018-01-01 and 2018-06-01 or 90 days ago for 30 days) using start_date and end_date can be tricky.

I’d begin with a {% if date_field._is_filtered %} just to ensure that the date field is being filtered, then move forward to check for nulls. So something like this may be a good starting point:

   sql_table_name: {% if created_date._is_filtered %}
                    {% assign cutoff = "now" | date: '%s' | minus: 7776000 %}
                    {% assign field_date = created_raw._end_date | date: '%s' | minus: 0 %}
                    {% if field_date >= cutoff %}
                        demo_db.users
                    {% else %}
                        some_other_table
                    {% endif %}
                  {% else %}
                    default_table
                  {% endif %} ;;

You may run into some issues with datatypes, depending on how your dates are being stored in your DB. Hope this is helpful :slight_smile:


(Kyle) #4

Hi @will.adams,

Sorry for the late response here. I have tried using this but all dates I filter for will automatically choose the else (in your example the some_other_table). As a note I am running this against a Redshift table.

Best,
Kyle


(will.adams) #5

Hey there @kpmartin87, yeah it looks like I was trying to compare to a date in the data, which does not exist yet at the time of query generation, whoops! Upon examining this mechanism more closely, the liquid {% date_start date_field %} generates SQL (not a date), so we wont be able to use it for this.

So one way to proceed might just be to use a parameter to allow the user to manually decide which table to pull form from:

parameter: date_range_param {
  type: unquoted
  allowed_value: {
    label: "Last 60 days"
    value: "rolling_sixty_day_table"
  }
  allowed_value: {
    label: "All Time"
    value: "all_time_table"
  }
}

Then you can use that value like so:

view: dynamic_table_view {
  sql_table_name: {% if date_range_param._parameter_value == "" %}
                    default_table
                  {% else %}
                    {% parameter date_range_param %}
                  {% end %} ;;
}

The top condition checks to make sure the parameter is being used, then the else beings in the value selected from the parameter as the table name. Note that it’s type: unquoted so that the resulting table name will not have quotes around it.


(Kyle) #6

Thanks @will.adams! Would this still work if a user changes the filter from a Created Date dimension or would the parameter have to live in the filter in a look?


(will.adams) #7

This parameter would be independent of the filters, so changing the field that’s being filtered would not change which table is being used. More on parameters here: https://docs.looker.com/reference/field-params/parameter#


(Kyle) #8

Is there anyway to force the parameter to be in enforced in all Looks and queries?


(Kyle) #9

Nevermind. I forgot about the Always Filter


(Kyle) #10

Thanks @will.adams for all your help on this!