5 Replies Latest reply: Feb 10, 2011 1:53 PM by Alex Botten RSS

    Help with complicated selections and exclusions

      Hi All,

      I have been using Qlikview for data exploration and spend analysis. It's a really great tool but I need some ideas around a particular problem.

      I select data using a variety of different fields and text searches to identify a baseline of spend for a particular category. My main issue is that I often have to use multiple searches and selections and I cannot apply them all simultaneously to get a single complete baseline in Qlikview. Furthermore if the searches produce overlapping result sets I want to remove anything previously selected so I don't double count.

      Of course I can export it to excel and build up the full set of data but this is not ideal and doesn't fully resolve the double counting. I've come up with a few ideas to get around this problem but I would appreciate expert opinion.


      Option 1) Export each search result set into Excel. Connect to these export files in the load script of the QV and match against the original rows. Where the row matches, mark the records with a flag, such as "selected". Use this flag in the QV front end to apply selections and exclude selections to ensure duplication doesn't occur.

      Advantages; Resolves the above issues around double counting and aggregating multiple complex selections

      Disadvantages; Fairly clunky process. Not sure how much could be automated


      Option 2) If possible write a script to attach the flag indicated above within QV. I don't know if this is possible as I don't know if I can use selected data as criteria for an update, and if the data model is updatable anywhere else other than the import script.

      Advantages; Keeps it all internal to QV and should be fully automated

      Disadvantages; Don't know if it is possible


      Option 3) Output the results of all selections to excel and run macros to identity double counted spend.

      Advantages; Resolves the issues mentioned above

      Disadvantages; Lose flexibly of QV to further interrogate final data selections


      I think this is a really interesting problem and would appreciate any feedback and experience of others.

      Alex B

        • Help with complicated selections and exclusions
          Liron Baram

          hi alex

          attach is an exmple of some sort of solution:

          the first part is using Bookmarks

          which allows you to save multiple choices as one choice

          the seconed part is using set analysis in the expressions it allow you to exlude double records

          check the attach file

          i mdae 3 bookmaraks if you select each one of them you can see what are the multiple selections are

          hope its helps you

          i'll be glad to help you if yu have mpre questions


            • Help with complicated selections and exclusions

              Thank you for your reply. I'm using personal version so I can't open your file however I think I understand your ideas but I don't think they address my issues.

              Bookmarks; Is it possible to UNION DISTINCT results of two bookmarks together, as this is essentially what I need to do.

              Set Analysis; The problem isn't in the data or the expressions used in the UI, its with how the selections are formed when data is being selected.

              I'll try and illustrate the problem as best I can with some examples;

              Case: Identification of spend for "Networking infrastructure" spend

              Search 1) Keyword search in transaction description field for "Networking" -> Yields results that match requirements

              Search 2) Selection based upon specific field value (not search but data selection, in this case Vendor Classification) - Yields results that match requirements but also includes some of the transactions found in the "Networking" keyword search.

              Search 3) Selection based upon specific field value (in this case GL code) - Yields results that match requirements but also includes data from Search 2 and Search 1.

              Each search yields additional data that is required but may also include data found in the previous search.

              What I need to do is find a way to select all of these search results at once so I can use the other reports I have to navigate this area of spend. I already use the set analysis to prevent duplication within the search but I cannot actually perform all of these searches at the same time as they overlap criteria and have multiple searches on the same field.

              If the searches are simple its not so bad as I can use the "select exluded" facility to deselect records already identified but there must be a more robust way around it.

              I was thinking that if I can identify in the base data the rows returned by the various searches, I could flag them and then include / exclude them as I need to so I'm trying to find the best way of achieving this.

              Thanks for your help!

                • Help with complicated selections and exclusions
                  Liron Baram


                  but that exctly what i've done with the bookmark

                  think of that like this

                  make a book mark for each case you described

                  let say each bookmark gives you a list of pepole

                  bulid a chart

                  with the pepole id as dimension

                  and in the expressions put sum({BM01}Spend),then sum({BM02-BM01}Spend),and so on

                  this way the second expression take all the pepole from the seconed case which are not in the first case ext

                  hope that clerify what i did

                  give it a try if not il give you a doc with the script and the expressions i used


                    • Help with complicated selections and exclusions

                      Ahhh ... I see. I didn't know you could reference bookmarks in expressions. Is it possible to create bookmarks programmatically and build the expressions programatically. I assume it is but I don't know how open the API is.

                      I'll have to think of the best implementation for this because as it stands it wouldn't work too well in the UI. It would be handy if I could create a button when each time it is clicked it creates a bookmark for the current selections, and then updates all of the expressions on a particular tab to include all of the bookmarks. I could then create an accumulated data output tab that used the expressions data.

                      You have pointed my in the right direction, thanks! Any further advice regarding the automation / scripting would be welcome.

                      Alex B.

                        • Help with complicated selections and exclusions

                          This is what I have quickly hacked together;


                          sub outputBookmarks
                          bookmarks = ActiveDocument.GetDocBookmarkNames
                          for i = 1 to UBound(bookmarks)
                          strSetExp = strSetExp & "[" & bookmarks(i) & "]+"

                          strSetExp = Left(strSetExp,Len(strSetExp)-1)
                          strSetExp = "{" & strSetExp & "}"

                          set objST = ActiveDocument.GetSheetObject("CH89")
                          objST.AddExpression "Sum(" & strSetExp & "NET_Amount)"
                          end sub

                          It builds the expression ok but when I look at the table it has a big cross through it. I click on the table properties and just click on ok to close it again, and it loads the data fine just as I want it.

                          Do I need to do a programmatic refresh or repaint? Am I missing something?