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! Go to Solution.
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 %}
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 %}
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}
}
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..
@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
}
}