Can we restrict filter condition options for strings?

kv3
Participant I

Hi,

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

efc90074-a8f1-4822-b16b-be94f9324bf6.png

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.

Solved Solved
1 7 2,472
1 ACCEPTED SOLUTION

Naomi_Johnson
Participant III

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:

30eea554-16a8-40c8-987b-24aba2a87a74.png
4aab1c00-c29d-4c9d-9559-ec240152fd6c.png

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

Naomi, Redkite

View solution in original post

7 REPLIES 7

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} ;;
}
99f164ae-4831-4d14-9d4e-42790b54a603.png

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

https://community.looker.com/getting-started-1006/feature-requests-how-your-feedback-makes-looker-be...



Please let me know if you have any questions.

Thanks,

Eric

kv3
Participant I

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.

Naomi_Johnson
Participant III

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:

30eea554-16a8-40c8-987b-24aba2a87a74.png
4aab1c00-c29d-4c9d-9559-ec240152fd6c.png

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. 

Ramona1
Participant I

^ encountering the same issue and it creates a lot of confusion b/w customers. it would be great if this can be controlled in the LookML 

moebe
Participant V

here too.

Different problem, same need.

We face the problem that we need a filter (i.e. field a = "X" or field b = "X"), which is not possible.
We could use a lookml field "Filter", but that would not be reflected in the aggregate overview. 
But if we could limit the filter options to e.g. contains, we would have a solution by creating a linked field....
 

Hi all, 

If helpful, here is slightly different solution that leverages array functions (in this case Snowflakes) to turn the comma delimited string data (from the example) into an array and looks for the intersection between the user inputted parameter data and the data from the column. This method works well in the case you want the data to be an exact match, i.e. filter for any string that has either the exact word ‘looker’ or ‘lookml’: 

Example of Data Function: Split(data , ‘,’)
looker, bigquery, lookml, explore [
  "looker",
  " bigquery",
  " lookml",
  " explore"
]
snowflake, looker [
  "snowflake",
  " looker"
]
lookml,looker
[
  "looker"
]

Users will input a comma delimited string like the below: 

673ed89e-fe38-46e6-ba08-a63626bdeafe.png

LookML:

  # Parameter where user enters in the strings users want to filter for
# Example: 'looker,lookml'
parameter: param_comma_delimitted_string {
type: string
}


dimension: is_params_in_data {
type: yesno
# Column data example: 'looker, bigquery, lookml, explore'
sql: case when ARRAY_SIZE( array_intersection(split(${my_column_dimension},','),
split({{ param_comma_delimitted_string._parameter_value }},',')
)
)> 0 then true else false end ;;

And a screenshot breaking out how each function rolls up together :

b9c80eae-24e1-48cd-ac59-b42eb77d8ca6.png
Function Breakout

You can add extra string cleaning function to remove whitespace / upper or lowercase as needed with something like lower({{ param_comma_delimitted_string._parameter_value }}) etc… prior to the split function turning the string into an array. 

Top Labels in this Space
Top Solution Authors