Button to search and exclude 1 value

[#1]

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?

[swuehl · Accepted solution]

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

[#3]

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]

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

[#5]

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