topic Re: Search Option, Dynamic filtering? in QlikView

Search Option, Dynamic filtering?

jchambers123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Does Qlikview have any option that allows a user to input a list of values, and then the program 'selects' (or filters) based off that list? I've found nothing online and in the technical books but hoping someone may have a solution.

...Here's some background for my question:

The app that I'm building will be primarily used by data analysts within my company (at least the initial phases of the app). Very often they have long lists of codes that they must filter the resulting data set by. Using a drop-down menus and manually selecting by the relevant values in the Qview app would be tedious and far too time consuming for lists as long as they are working with. So we're looking for an option to manually-input those value lists and have the relevant values selected by the program. And the lists change very rapidly (i.e., new values are added/dropped on the fly) so "bookmarks" or flag variables aren't are very good option.

Anybody have any ideas for a solution? And if yes, can you please provide a brief example of code?

Thanks much in advance,

-John

Re: Search Option, Dynamic filtering?

kaushiknsolanki — Wed, 09 Aug 2017 17:54:19 GMT

Hi,

One question: is every user will have their own set of filters?

Regards,

Kaushik Solanki

Re: Search Option, Dynamic filtering?

jchambers123 — Wed, 09 Aug 2017 17:56:07 GMT

Hi Kaushik,

Yes, every user would have their own set and they would change rapidly and thus need to be dynamic

Thanks, -John

Re: Search Option, Dynamic filtering?

effinty2112 — Wed, 09 Aug 2017 18:50:31 GMT

Hi John,

If your analysts can have their selections as a column in an Excel sheet with the first cell being the name of the field the selections are to be made in you can try this:

Add an inputbox to your front end and add a variable vList to it.

Copy a column out of your Excel sheet where the first cell is the name of the field and the rest are the values.

Paste that into the inputbox.

IMPORTANT: When you post into the inputbox you must do this. Click on the input box then click the grey button with the ellipsis (...) so you get the edit expression screen and paste in there. If you do that the data will be pasted in as a column. If you paste straight into the inputbox the data will not be accepted as a column.

Add these variables (not in the inputbox, use the Variable Overview)

vField =

=SubField(Replace(Trim(vList),chr(10),'|'),'|',1)

vValues =

='(' & chr(34) & Replace(Right(

Replace(Trim(vList),chr(10),'|')

,len(Replace(Trim(vList),chr(10),'|'))

-Index(Replace(Trim(vList),chr(10),'|'),'|')),'|',

Chr(34) & '|' & Chr(34)) & chr(34) & ')'

===========================================================

EDIT 11 Oct 2018 - This is an alternative that adds '*' wildcard characters to the search string

vValues =

='(' & chr(34) & '*' & Replace(Right(
Replace(Trim(vList),chr(10),'|')
,len(Replace(Trim(vList),chr(10),'|'))
-Index(Replace(Trim(vList),chr(10),'|'),'|')),'|',
'*' & Chr(34) & '|' & Chr(34)& '*') & '*' & chr(34) & ')'

===========================================================

When defining these do so including the leading '=' in the definition. In the Variable Overview the functions will appear blue.

Nearly there.

Add a button, label it Apply Filter or something. Add the action Select in Field.

for Field enter : =vField

for Search String enter : =vValues

Now try it out.

Good luck

Andrew

Re: Search Option, Dynamic filtering?

jchambers123 — Thu, 10 Aug 2017 17:26:34 GMT

Andrew,

I tried what you suggested and it worked perfectly. Thank you so much-- great suggestion!

Best,

-John

Re: Search Option, Dynamic filtering?

effinty2112 — Thu, 10 Aug 2017 19:13:06 GMT

Hi John,

Very pleased it works for you.

Cheers

Andrew