String Expression Evaluation in Straight Tables

[#1]

We are building a system that will have a table of expressions (value and limit) that need to be evaluated. When "value">"limit" an alert will be generated. We are using "$" expansion on a pick expression that gets evaluated across the alerts and multiple dimensions (see attached toy example). The real data model is much larger with more dimensions and several million records.

We build pick expressions to evaluate "value", 'limit", and "value>limit". The last one is used to build a count by Alert Name or by a specific dimension for that Alert.

The issue we are arriving at is the evaluation seem to blow out memory usage, going from 1GB to over 7GB when evaluating these expressions, and takes far too long to build the straight table on a small set of alerts. I am not sure if Qlikview caches all of the calculations for each dimension in memory to make selections faster.

Any help or advice on memory usage and/or better evaluation techniques would be much appreciated.

Thanks

[Oleg_Troyansky]

Peter,

I never benchmarked the performance of a Pick statement with so many embedded formulas, so I can't quite comment on that, but I noticed a quite extensive use of AGGR in your formulas, and it triggered an alert in my mind, considering your comment about millions of records...

While AGGR is a wonderful feature in QlikView, it can lead to a huge performance issue, when used carelessly. Examine your AGGR formulas and try to predict the number of possible values that the AGGR will have to produce. You should be safe if the number of distinct values is in thousands or even tends of thousands. You need to get more cautious if the number goes into hundreds of thousands, and you should think of another solution if you are looking at millions distinct values.

In addition, I have a hunch that thew way you implemented your formulas, using Pick and flexible functions, you might be causing each cell in your Straight table to perform its own AGGR calculation, and that's causing the explosive growth in the memory consumption.

We all like dynamic solutions that are slick and elegant, but at times they are not very scale-able for larger volumes of data. In those cases, we might have to resort to simple, even if less elegant, solutions.

Oleg Troyansky

www.masterssummit.com

[#3]

Oleg,

Thank you very much for your feed back. I will rework my solution and be mindful of the use of aggr's in my functions.

Much appreciated.

Pete