Negative Search in Looker Input Control

[nat547]

I'm using an input control in Looker and need to exclude the text entered into it.

This will be a way to exclude branded search from the Query field using Search Console data. I.e. If "apple" is entered into the text input, the table will exclude any queries with "apple" in it. I've tried parameters and most recently tried setting input search type to Regex with a calculated field (below) that negates the query string entered, but I don't think I have this right.

CONCAT("^(?!.*",LOWER(Query),".*$).*$")

Has anyone successfully used an input control to EXCLUDE an entered string instead of INCLUDE it?

[GavinW]

 Have you tried using a filter and setting it to 'exclude'? See point 7 in the linked documentation.

[nat547]

That'd be the easy option! But no, this needs to exclude client-entered terms, so it need to exclude what they type into the input.

Unless I can create a filter that excludes whatever value they enter into that input?

[GavinW]

I believe you can do this with an input box control and then use that as an input for an advanced filter. After you have created the advanced filter, there should be an option to 'select a field or value' where you can choose the name of your input box to supply a value to the filter.

[nat547]

I'm not seeing that as an option that would work. Here's what I see under the options for an Advanced Filter Control. Ideally, I'd love to select Query as the control field and set search type to "does not contain" and I'd be set. But unfortunately, that's not an option.

As you're saying, I could pair an advanced filter with a regular input control that uses Query as the control field (let's call that input control "ExcludeWord"). Then, I could create an advanced filter that uses ExcludeWord as the control field. But I still wouldn't be able to exclude that term as far as I know because the search types are Equals/Contains/Starts With/Regexp/In.

How would you handle this?

[lauratilton]

Here's one approach that should give you what you want:

Create an text parameter called Input and set up an input box so the user can enter a term, then set up the other tables/charts you need (all records will show).

Create a calculated field called ContainsInput using the formula CONTAINS_Text(Query, Input)
This will return True when the Query contains the user input and False when it doesn't.

Then, set up a report level filter to Exclude where ContainsInput is True which will prevent all the Query values that contain the user input from displaying.