4 Replies Latest reply: Oct 26, 2012 12:24 PM by Adrien Allard RSS

    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?

       

        • Re: Button to search and exclude 1 value
          Stefan Wühl

          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

            • Re: Button to search and exclude 1 value

              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

                • Re: Button to search and exclude 1 value
                  Stefan Wühl

                  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

                    • Re: Button to search and exclude 1 value

                      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