Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
jchambers123
New Contributor II

Search Option, Dynamic filtering?

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
effinty2112
Honored Contributor

Re: Search Option, Dynamic filtering?

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Search Option, Dynamic filtering?

Hi,

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
jchambers123
New Contributor II

Re: Search Option, Dynamic filtering?

Hi Kaushik,

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

Thanks, -John

Highlighted
effinty2112
Honored Contributor

Re: Search Option, Dynamic filtering?

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

View solution in original post

Highlighted
jchambers123
New Contributor II

Re: Search Option, Dynamic filtering?

Andrew,

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

Best,

-John

Highlighted
effinty2112
Honored Contributor

Re: Search Option, Dynamic filtering?

Hi John,

Very pleased it works for you.

Cheers

Andrew