Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jchambers123
Contributor II
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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jchambers123
Contributor II
Contributor II
Author

Hi Kaushik,

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

Thanks, -John

effinty2112
Master
Master

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

jchambers123
Contributor II
Contributor II
Author

Andrew,

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

Best,

-John

effinty2112
Master
Master

Hi John,

Very pleased it works for you.

Cheers

Andrew