topic Re: Performance tuning - aggregating tables and average values in QlikView

Performance tuning - aggregating tables and average values

rigosong — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

currently I'm encountering performance issues with one of our QlikView applications, due to the amount of data.

Situation (simplified) - Table with date and value columns:

Date	Value
01.01.20	1,356
01.01.20	2,121
01.01.20	1,640
01.01.20	1,443
02.01.20	1,495
02.01.20	1,881

User Need: Calculate average value depending on picked date range.
Eg. user picks date range 1.1.20 - 2.1.20 and should get the result 1,656.

To get this done with a table and expression is straight forward. Date as Dimension and AVG() Expression for Value.

However, due to the massive amount of data the Qlikview application takes for ever loading and processing user inputs.

My thought was to transform the original tables into already aggregated tables where the values are grouped by date. The table size would be reduced imensly with this.

This idea with the above example:

Date	Avg.Value
01.01.20	1,640
02.01.20	1,688

The Problem with this: if the user picks again the date range 1.1.20 - 2.1.20 the result for average value would be 1,664 but it should be 1,656.

So, how would you tackle this problem?

Re: Performance tuning - aggregating tables and average values

Arthur_Fong — Fri, 17 Jan 2020 09:07:51 GMT

Try this:

Script:

Raw: load * inline [ Date Value 01.01.20 1356 01.01.20 2121 01.01.20 1640 01.01.20 1443 02.01.20 1495 02.01.20 1881 ](delimiter is ' '); let vRow=NoOfRows('Raw'); NoConcatenate Data: load Date,'$(vRow)'as RowNum,sum(Value) as Value resident Raw group by Date; drop table Raw; exit script;

Re: Performance tuning - aggregating tables and average values

jonathandienst — Fri, 17 Jan 2020 09:43:44 GMT

If the by date aggregated table contains the total value AND the number of entries for the date, you can get the original average by summing the totals and dividing by the sum of the entry counts.

Re: Performance tuning - aggregating tables and average values

rigosong — Fri, 17 Jan 2020 10:00:04 GMT

Thanks @Arthur_Fong , this works 🙂

Re: Performance tuning - aggregating tables and average values

rigosong — Fri, 17 Jan 2020 10:01:08 GMT

Thanks @jonathandienst ! Same approach as @Arthur_Fong and it works 🙂