Best way to specify large number of filter values?

[#1]

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

[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

[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

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

[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.

[#5]

Thanks for the reply Marcus. I'm very new to QlikView so I'm not sure what that dialog box is that you have a screenprint of. Can you elaborate please?

[#6]

Thanks for the reply Patrik. I'm not sure whether it should be an extension or not. Since I'm so new to QlikView I wasn't even sure where to post the question. Since it's not functionality that's available out of the box, I figured it must be an extension since my understanding is that extensions are how you add more functionality to QlikView.

[#7]

Thanks for your reply Josh. Can you point me to where that extension is documented. I searched QlikView for "CopyPasteSearch" and only found one hit: your reply to my question.

[Josh_Good · Accepted solution]

This extension isn't documented it is just something that was created as a sample of what could be done with extensions.  If you download the file from above and double click on it QlikView will install the extension.  Once the extension is installed you must enter WebView in QV (View -> Turn on/off WebView) to access extensions.

To add the actual extension to the QVW:

right click on any unused area of the app.

select "New Sheet Object"

Expand "Extension Objects"

Drag "Copy Paste Search" object on to the worksheet

right click on the object, select "Properties" and set the properties as needed.

[#9]

Thank you Josh. That's exactly what I was looking for!!