8 Replies Latest reply: Sep 19, 2013 3:29 PM by David Pantich RSS

    Best way to specify large number of filter values?

    David Pantich

      Hello,

       

      Looking for a way to provide users the ability to specify a large number of filter values without typing them all in or selecting them all from some filter box. Let's use an example:

      1. Data model has a table of millions of customers
      2. Also has another table of many more millions of rows listing usage by those customers
      3. Client wants has a list of (for example) couple of thousand customers that they want to filter by. These customers have nothing in common. In other words, there is no flag, indicator or some value in any column that could be used to limit it to those customers. Client wants to see the usage records for just those customers

       

      I can think of a couple of ways to do this but I don't know how to accomplish this in QlikView:

      1. Client points to a file (plain text or spreadsheet) containing account numbers of those customers
      2. Client copies a comma separated list of the account numbers from somewhere and pastes them into a text box that can be used by QV to filter

       

      Any ideas of how I might accomplish this? I was thinking maybe an extension but I don't know how to do it. I thought I saw an extension that implemented method #2 from above but I can't find that now.

       

      Thanks in advance for any help


        • Re: Best way to specify large number of filter values?
          Marcus Sommer

          Both methods could work.

          By the first will be required that the user could initiate a reload and had this also as disadvantage in regard to time-spent, traffic and if could use this app use more as one user they could disturb each other. But you could also work with two/several apps.

           

          The second method is easier you need only a variable in a inputbox an with copy from the customer-list the variable changed and triggered a selection (the syntax from the list is important - also I don't know if there is limitations regards to the size of the list).

           

           

           

          - Marcus

          • Re: Best way to specify large number of filter values?
            Patrik Seger

            Hi David

             

            Since you ask for help in Extension group, I guess that creating a macro script is not an option?

             

            To do this in an extension, you may solve it like:

            1. If you have a file located at a webserver somewhere in your clients organization, you could create an extension object that enables browsing among these files, and when selecting one of them, read the values from the file and use them to make a selection in the field.
              Not the best of options since you probably do not want the files on a webserver, kind of static approach.
            2. The second option is better, but requires newer web browsers at your client. Have a look at the following API description how to handle local files: Reading local files in JavaScript - HTML5 Rocks

             

            To make the selection, I guess you would use some of the methods in the Class Qv.Document.Object.Data, such as

            Capture.PNG.png

            I would go for SelectTextsInColumn or SelectValuesInColumn and see to it that the dimension you are searching for is defined in the Extension Object. I'm though not sure that QlikView is stable enough to handle that magnitude of selection data, so I would start in that end and test first.

             

            There are probably more and maybe better ways, but this is what I came up with from the top of my mind.

             

            Br Patrik

            • Re: Best way to specify large number of filter values?
              Josh Good

              I would recommend using the CopyPasteSearch extension.  This will allow users to copy the list of values into QlikView and then select all the values. Credit to Anthony Deighton who created the extension.