Search box performance problem

[asaiel20]

Dear,

Hello everyone, I hope you can help me out with this search box performance issue:

We have design a Qlikview document to represent the possible item codes and their description. The document follows the CSI Master Format for item coding and classification. It loads the data from multiple excel sheets each representing a class or div in CSI format. Users are searching possible items using a search box linked to six possible description parts each part represents an item specification. However, with our current design the search box is taking unusual long time to show the results and in worst case the whole document freezes.

In the design all excel sheets are loaded into one master table called “FromExcel”. Then all CSI attributes are derived from it building all possible combination of item codes and their description. The following are the searchable fields added to the search box:

1. desc1
2. desc2
3. desc3
4. desc4
5. desc5
6. desc6

Any possible item’s description will be the combination of those six pieces. Attached is the structure image. Is there any way to make the search box faster? your help is highly appreciated.

system versions: Qlikview client v12.10, Qlikview Server v12.10.20600.0

[Jacek]

Did you try to connect these tables to the one common table but only with field Flag (1-6) ? Then you could select only 1 field for search. 

[asaiel20]

Dear Mr. Jacek,

Thank you for your help,  but I'm not sure I got what you want me to try. two Qs:

which one common table?

what do you mean by field flag only?

Don't forget that I need the user to be able to select and filter the data directly from the search box.

thanx again for you time

[Jacek · Accepted solution]

instead of these tables with key2, key3 ... one commont table with flag column that has information about number of key. Between additional bridge table for connection. This is only draft of concept schema, in Qlik you should create one key between tables.

[rwunderlich]

I expect your problem is not with the search box, but that your FromExcel table is a data island.  I recommend you pursue @Jacek suggestion to make your search fields link into the model. 

-Rob

[asaiel20]

Dear Jacek,

I found your answer very helpful, however, in my case I still want the user to be able to select multiple description. have a look at the UI attached. The procurement user should be able to combine multiple description to build up the item code. with your model, when I combined the description pieces, selecting one description eliminates other descriptions.

So, do you think I still have to merge desc2 ... desc6 and sort it out in the front end some how?

Thank you 

[asaiel20]

Thank you very much for your help.

you are right about two things:

• In Qlik you should create one key between tables: this help me a lot in doc size
• One table with a flag field which combine the descriptions: it did the trick for the search box performance.

thanx again