User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Solved

Dynamic filters (List of numbers)

  • 10 February 2021
  • 7 replies
  • 100 views
dingo

Hello, I have a requirement where I need to allow users to enter program numbers dynamically and I should take those numbers and display results.

For Ex, In the derived table SQL, 
SELECT * FROM TABLE WHERE programId IN (1,2,3,4,5,6);

I want to allow the program numbers to be entered by users dynamically. i.e, Users should enter 1,2,34,5,6.

How do I achieve this in Looker? I cant use Templated filters  since they are only for logical results (i.e, program =’1’ ). Can I use parameters in SQL derived table? I couldn’t find documentation for doing this with parameters too. 


Can someone please help? Thanks in Advance.

icon

Best answer by David_P 10 February 2021, 08:52

Hello @dingo 

You are on the right track! 

Parameters is not an option in this case as it does not allow to pass multiple values.

But templated filters should work. Whether a user enters one value or multiple values, it will adjust the query to  “= value” or “in(value1, value2, ...)”.

 

Something like this should work:

  filter: number_list {
    type: number
    default_value: "1"
  }

...and then just add the templated filter in your derived table.

{% condition number_list %} sql_or_lookml_reference  {% endcondition %}

 
View original

7 replies

David_P
Userlevel 2
Badge

Hello @dingo 

You are on the right track! 

Parameters is not an option in this case as it does not allow to pass multiple values.

But templated filters should work. Whether a user enters one value or multiple values, it will adjust the query to  “= value” or “in(value1, value2, ...)”.

 

Something like this should work:

  filter: number_list {
    type: number
    default_value: "1"
  }

...and then just add the templated filter in your derived table.

{% condition number_list %} sql_or_lookml_reference  {% endcondition %}

 
David Peña
dingo

Hi @David_P  

Thanks for responding. I tried it and it worked perfectly. Thanks a lot.

This is the last thing I need, Is there any way to do the similar trick for following case : 

age BETWEEN 19 AND 21 

OR BETWEEN 27 AND 30

OR BETWEEN 32 and 35

 

I want to give users the option to enter :

BETWEEN 19 AND 21 OR BETWEEN 27 AND 30 OR BETWEEN 32 and 35

 

Is this possible ? 

 

Thank you.

LookVijay

Yes you can use Dimension of Type Tier and then filter out of it.

 

dimension: Age

{    

type: tier

tiers: [0, 19, 27, 32, 35]

sql: ${Age} ;;

style: integer  

}

 

Filter: Age_Tier

{

suggested_dimensions: Age

suggested_explorer: {Explore Name}

}

dingo

hi @LookVijay , I want to allow users to enter date ranges instead of hard-coding it in lookML. 

For instance, users can enter any dynamic value, like this whole string for example: 
BETWEEN 19 AND 21 OR BETWEEN 27 AND 30 OR BETWEEN 32 and 35

 

I want to take that string and insert it into SELECT * FROM TABLE WHERE age (<take users input here>);

LookVijay

May be this logic of using parameter may help not sure..

 

dingo

@LookVijay  Above logic will just have a drop down in UI with values (specified in allowed values i.e, category, conservation_status, common_names). It will not allow users to enter input dynamically.
Please correct me if I m wrong.

LookVijay

I’m fairly new as well and learning and I think you can use template filters like the syntax shown below. You may need to create a derived table with persists statement.

 

view: view_name {

derived_table: {

sql:

SELECT * FROM TABLE WHERE age BETWEEN

{% condition age %} table.age {% endcondition %} AND {% condition age %} table.age {% endcondition %}

;;

}

filter: age {

type: number

}

}

Reply