Dynamic filters (List of numbers)

dingo
Participant I

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.

Solved Solved
0 7 1,877
1 ACCEPTED SOLUTION

David_P1
Participant V

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 solution in original post

7 REPLIES 7

David_P1
Participant V

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 %}

 

dingo
Participant I

Hi @David_P1  

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.

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
Participant I

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>);

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

f15d1095-7f7d-4338-8edb-dea76be411c8.png

dingo
Participant I

@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.

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

}

}

Top Labels in this Space
Top Solution Authors