Solved: Self reference Filter value

Paul_Vienneau · 10-29-2020 06:31 AM

Hello,

With the hopes of restricting my result set based on a user entered value, I created a view filter with a SQL parameter - simplified for this example:

filter: test_filter_sql {
type: number
sql: 1 = ${test_filter_sql};;
}

Question: How do I reference the entered value of the filter in my SQL statement The above gives me a circular reference exception, which is understandable.

Thanks!

-paul

nathaniel_t

You’ve got it nearly perfect there, Paul! The one small change we’ll want to make on this is using a different Liquid variable inside of the if statement. Since Liquid if statements are already set aside as Liquid, we can’t use the {% parameter %} syntax inside of them to pass in the parameter’s value. Instead, we can use the (aptly named) ._parameter_value variable (more detail on this here in our docs).

So in our example, we can use something like this as our if statement:

{% if our_view.hi_im_a_parameter._parameter_value != "NULL" %}

View solution in original post

Dawid

Can you test it with Liquid?

1 = {{ value }}

Just a theory though, I haven’t tested it : )

Paul_Vienneau

thanks, but no luck - tried {{ value }} and { value }

nathaniel_t

Hey Paul! It sounds like {% condition %} will be the parameter to help us out here. With {% condition %}, we can apply the user’s input to the sql: of the filter itself, which in turn will apply to the WHERE clause of the Explore’s generated SQL. So as an example, we might have:

filter: hi_im_a_filter { 
  type: number
  sql: {% condition %} 1 {% endcondition %} ;;
}

Whenever someone in the Explore uses this filter, Looker will go ahead and generate the right SQL to filter the value (here it’s 1, but that could be a ${column_name} as well) to the condition(s) the user selects, and throws that into the WHERE clause!

The docs on filter have another more in-depth example, with some sample SQL Looker would generate as well! https://docs.looker.com/reference/field-params/filter#using_the_sql_parameter_with_filter

Paul_Vienneau

Thanks, @nathaniel_t for getting back to me. I think my example may be a bit more tricky.

Using your example, let me expand.

I have a view on a Date table - containing a sequential list of dates. On the view, I’m attempting to add a filter that will restrict the rows that come back in the results via the SQL param. Specifically, I’m applying a MOD function (Snowflake) that will give me every so many dates, based on the increment value they provide <INCERMENT VALUE>

For instance, if my base date is 3/5/2020 (example below), and the user entered 7, I would get back records containing 3/5/2020, 3/12/2020, 3/19/2020. etc.

filter: hi_im_a_filter {

type: number

sql: MOD(DATE_PART(EPOCH_SECONDS, ‘3/5/2020'::DATE) - DATE_PART(EPOCH_SECONDS,${vw_date.date_raw}), <INCREMENT VALUE>) = 0;;

}

Question is, how do I reference the value entered in the filter and substitute it in the example above?

Thanks again.

-paul

nathaniel_t

Ahh, clever! I see where you’re going with it.

For that I think we’ll want to switch tactics a bit, and use a parameter instead of a filter for this. A parameter would be a filter-only field for the user, except that it only allows one value in it, and Looker no longer generates the filter logic for us. Something to keep in mind is unlike a filter, with a parameter the sql: parameter is not supported, and we can’t directly influence the where clause. However with that, we now have the flexibility to get creative with where we use the value the user entered, with the {% parameter our_parameter_name %} syntax in sql: params.

From there, we could either filter the view itself by making it a derived table with our WHERE clause added in, or if we want to have it at the Explore level, we can add it in a sql_always_where to have the logic applied in the Explore query rather than in a derived table.

So as a high level example, we might have this parameter:

parameter: hi_im_a_parameter {
  type: number
}

and we could throw it in a derived table like this:

view: our_view {
  derived_table: {
    sql: SELECT * FROM my_cool_date_table 
         WHERE FANCY_SQL_FUNCTION(argument_1, {% parameter our_view.hi_im_a_parameter %}) = 12 ;;
  }

  parameter: hi_im_a_parameter {
    type: number
  }

  [and our dimensions and measures]
}

or in the sql_always_where of an explore like this:

sql_always_where: FANCY_SQL_FUNCTION(argument_1, {% parameter our_view.hi_im_a_parameter %}) = 12 ;;

There’s another example of this, using the sql: param of a measure here in our docs on parameters: https://docs.looker.com/reference/field-params/parameter#using_parameter_with_liquid

Paul_Vienneau

Thanks, @nathaniel_t, the Explore option worked - thanks.

However, I need to check for the presence of a parameter value as my FANCY_SQL_FUNCTION won't work with nulls. I’ve tried every combination I can think of to reference our_view.hi_im_a_parameter in the Explore but can seem to get it right.

sql_always_where:
{% if our_view.hi_im_a_parameter != “NULL” %}
FANCY_SQL_FUNCTION(argument_1, {% parameter our_view.hi_im_a_parameter %}) = 12 ;;
{% else %}
TRUE
{% endif %};;

Thoughts on what I’m doing wrong above?

nathaniel_t

You’ve got it nearly perfect there, Paul! The one small change we’ll want to make on this is using a different Liquid variable inside of the if statement. Since Liquid if statements are already set aside as Liquid, we can’t use the {% parameter %} syntax inside of them to pass in the parameter’s value. Instead, we can use the (aptly named) ._parameter_value variable (more detail on this here in our docs).

So in our example, we can use something like this as our if statement:

{% if our_view.hi_im_a_parameter._parameter_value != "NULL" %}

Paul_Vienneau

Thanks, Nathan - that was it.