Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
sduguet
Contributor III
Contributor III

Filtering a field from an external list under IE Pluggin or Ajax

Hi all,

I've users that have in a lot of lists of clients, or list of product reference, in excel files.

They would like to be able to filter in qlikview according to thoses lists.

And they could access the app through IE Pluggin or Ajax.

I've provided the following "tool",

- a parameter to select the field to filter,

- a parameter fo past the values from excel

- a button that launch a macro to apply the filter.

This is working fine for few values (hundreds) but for thousands, or more, it's a bit slow, or get stuck.

In the attached sample, you can generate 200000 row for the reload,

then try to select some values and filter them.

ex, filter col1 < 100, then select possible values in col2, clear the filters, past in the "Value to select" imput field, and hit the button.

The first bottleneck is the Imput field. Pasting 10 000 values inside and you need to wait for the application to digest the past.

Then there is the perf of the Match. The first version was using select and toogleselect with a loop. It was taking too much time. So I've replaced this by the Match.

sample figures

For 200000 lines, filtering 10000 take 28 sec (+ 20 sec to past the data in the imput field)

For 1000000 lines, filtering 10000 take 2 min 25 sec (+ 20 sec to past the data in the imput field)

Do you have any idea that could

- improve this function ?

- replace this ?

Best Regards,

Stephane.

5 Replies
Not applicable

*Edit - I published to our server and it works fine.  For whatever reason it just won't work in the desktop webview*

Hi Stephane,

Sorry, I don't have an answer for you but I do have a question about this.  Are you able to get this to work in the webview mode?  I got it to work fine in desktop but when I switch to webview the filter won't apply.

Thanks!

- Mark

Message was edited by: Mark Ketchaver

RSvebeck
Specialist
Specialist

This is possible to solve completely without using macro. See attached soluition. It will work on any platform I assume.

Best Regards

Robert

Svebeck Consulting AB
sduguet
Contributor III
Contributor III
Author

Hi Mark,

Yes it work on the Ajax mode, but in this caase you have to be carefull of the separators, as the Paste of the values in the fields directly from excel do not work. You need to have a comma or 2 spaces as field separators.

Separators used could be edited in the first part of the macro.

Best Regards,

Stephane.

sduguet
Contributor III
Contributor III
Author

Hi Robert,

It's a neat solution that's even quicker, I will do additionnal test with more values.

Renmain the Paste  that is not user friendly as we don't know when the client has finished to "accept" the data in the imput field (when we past a lot of values, like 10000 to 40000.

any Idea about this ?

for My usage, I've changed :

a)

I've written the formula directly in the action Select in field, Search String, because the display of the calculated result in the 2nd variable was taking time.

So I also removed this second variable.

b)

I've updated the replace, to macth my needs

(as pasting from excel could use the CHR(10) separator, and add an additionnal separator at the end that need to be removed.)

=replace(

'(' &

replace(replace(replace(replace(replace(vToSelect,',','|'),';','|'),'  ','|'),chr(13)&chr(10),'|'),chr(10),'|') 

& ')'

,'|)',')')

c)

I've added two actions in the button,

Select Possible =vFieldFilter

to change in the Imput box the filter from (xx|xx .. to the "n of n" so the user could have the count of the selected value for control.

Set Var =vFieldFilter to empty

because a lot have values in the imput box (10000 values) slow down the interface, so I clear the data pasted once filtered.

RSvebeck
Specialist
Specialist

Hi Stephane,

For very large sets of filter-lists, you should consider using textfiles or excelfiles instead together with the partial reload concept.

1 User store the textfile on a shared folder

2 Qlikview reads this textfile during reload / partial reload on a regular basis

3 The filter will be part of your datastructure, and as fast as anything else in Qlikview.

You may have to work with unique folders for storing the textfiles per user so that each user has its own filter.

Brg

Robert

Svebeck Consulting AB