Solved

Can we restrict filter condition options for strings?

  • 11 November 2020
  • 4 replies
  • 691 views

Hi,

I have a filter for string type and in the dropdown it shows 

the options based on which we can filter, Can we restrict these options for users somehow either through lookml or the ui?

The string column named “hashtags” might contain a list or a single word like:

  1. looker, bigquery, lookml, explore
  2. snowflake, looker
  3. looker

SO, when someone tries to filter with hashtag “is equals to” looker, it will show only 1 row  but when it will be like  hashtag “contains” looker, it will give all the 3 rows.

So, I want to restrict the option “is equals to” from the above list so that customer does not gets confused.

 

For example: say I just want the customer be only be able to filter through “contains” and disable/remove all other options for this filter.

 

or

is there a way in lookml where I can put condition within filter like say if customer choses “is equals to”, then filter using this sql condition , other wise use the other sql condition.

icon

Best answer by Naomi_Johnson 11 November 2020, 16:39

View original

4 replies

Userlevel 4
Badge

Hey @kv3 

 

We cannot apply a limit on the dropdown by default, but we can use a parameter type: string as a workaround. This way the user can really only input a value. We can then model the specific filter syntax within the LookML. For example, if I want to have users only be able to filter on fields that contains with a specific letter or string. I can allow them to pass in some input via a parameter. We can label this CONTAINS FILTER or something to make it obvious to users. Then, I take that input and concatenate it with % to leverage the wildcard syntax (The dialect for my example is MySQL) in a second dimension. Then, I pass this new concatenated fields into a yesno dimension. I can add a sql_always_where on the explore level to make sure that the explore is always filtered on yes. This workflow would also work for a parameter and you can try to inject the value within a derived table where you manually code the specific filter syntax. 

 

In the example below, the yesno dimension only evaluates to yes for states that contains the letter W since that is what the user passed in the value of parameter_named_string. We can adjust the syntax to to work with a syntax for begins with, ends with, or contains. 

 

We could extend this example and make the filter conditional on another parameter. For example, we can create a parameter with allowed values of contains, begins, ends, or is equal to. We can then use these to create a conditional that determines if we use a LIKE in the yesno dimension or where we add the wildcard values to the string. 

parameter: parameter_named_string {
type: string
}

dimension: contain_string {
type: string
sql: CONCAT('%',{% parameter parameter_named_string %},'%') ;;
}

dimension: yesno_filter {
type: yesno
sql: ${state} LIKE ${contain_string} ;;
}

 

 

 

I listed this use-case in our internal tracking, but please feel free to provide some more feedback or upvote the request here.
 



Please let me know if you have any questions.

 

Thanks,

Eric

Thanks for replying @Eric_Lyons , This will help in 1 condition say like “is equal to” or “contains”,

my requirement is to have both say “equals to” and “not equals to”. Is there anything that can do both because with parameter it will always be “is equal to” . It wont handle “is not equal to” case.

Userlevel 2

Hi @kv3 

Adding on to @Eric_Lyons’s answer, you could create one more parameter which allows the end user to choose ‘Equals to’ or ‘Not Equals to’, which changes the sql in the yesno dimension based on the user’s chosen value

  dimension: contain_string {
    type: string
    sql: CONCAT('%',{% parameter parameter_named_string %},'%') ;;
  }
  
  parameter: contains {
    type: unquoted
    allowed_value: {label: "Equals to" value: ""}
    allowed_value: {label: "Not Equals to" value: "NOT"}
  }

  dimension: yesno_filter {
    type: yesno
    sql: ${state} {% parameter contains %} LIKE ${contain_string} ;;
  }

I’ve added some screenshots of how it would be used below:

 

I hope this helps. Let me know if you have any questions

Naomi, Redkite

My customer is facing the same issue as well. It would be good for us to control the  dropdown options using lookml codes to avoid customer using the wrong dropdown by mistake. This will reduce tickets / enquiries raised by the customer. Would be great for Looker bring back to the engineering team for this feature as I am sure many people would benefit from it. 

Reply