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: 
Not applicable

Button to search and exclude 1 value

I have a button set up that currently does this...

(5*|6*|2*|*118429308*)

It takes all data in my field that starts with 5, 6, 2, and is 118429308.

I have another button that does this...

(1*|0*|8*|7*)

It takes all data in my field that starts with 1, 0, 8, and 7. However, I want it to exclude the number 118429308 even though it starts with a 1.

How do I do this?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe I am missing some simpler method (I tend to stick to my habits in this IMHO poorly documented search features), but you should be able to do it like this:

Replace your search string with (including the equal sign and the single quotes!):

='=sum({<FIELD={"(1*|0*|8*|7*)"}-{118429308} >} 1)'

where you need to replace FIELD by your field name you want to search in (the same name you already used in your button dialog).

Hope this helps,

Stefan

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe I am missing some simpler method (I tend to stick to my habits in this IMHO poorly documented search features), but you should be able to do it like this:

Replace your search string with (including the equal sign and the single quotes!):

='=sum({<FIELD={"(1*|0*|8*|7*)"}-{118429308} >} 1)'

where you need to replace FIELD by your field name you want to search in (the same name you already used in your button dialog).

Hope this helps,

Stefan

Not applicable
Author

That worked perfectly. Thank you! Although I'm having a hard time figuring out how it did it!

New to qlikview and sql in general

swuehl
MVP
MVP

Yes, must be pretty hard to read for a starter, I think..

This expression essentially uses a set expression to create a kind of temporary selection in FIELD (only in the context of the sum() function), and using the sum function's result  0 or 1 as logic to filter the FIELD values in the application.

To understand set analysis / set expressions will probably take some time, but it will pay off. It allows quite easily to manipulate selections / sets and can be widely used in QV.

There are some advanced search terms that might be easier to understand, if you manage to read them correctly, like

='=wildmatch(Value,'&chr(39)&'1*'&chr(39)&','&chr(39)&'2*'&chr(39)&') and not Value=1'

should list all FIELD values that start with one or two except one itself. The chr(39) is needed to get the single quotes around the wildcard search terms '1*' and '2*' in the search string.

Hope this helps a little bit,

Stefan

Not applicable
Author

it also helped me :

i) To apply a "advanced search" sentence inside a button action, one must place it inside quotes

='=rank(aggr(sum(Sales),Customer))<=10'   

(this selects the top 10 customers)

ii) To use set analysis in order to make a selection in an "advanced search" sentence, on can use sum({ } 1)

=sum({<FIELD={"(1*|0*|8*|7*)"}-{118429308} >} 1)

iii) to escape the quote ( ' ) character, one can use the & and the chr(39)

='=wildmatch(Value,'&chr(39)&'1*'&chr(39)&','&chr(39)&'2*'&chr(39)&') and not Value=1'

Thanks a lot !!

Adrien