topic Re: Aggr() in UI vs Group by in script in QlikView

Aggr() in UI vs Group by in script

surajap123 — Fri, 02 May 2014 16:11:49 GMT

Hi All,

My aggregate table created in qlikivew script generates lot of rows, as a result the data reload takes long time.

I am thinking to use Aggr function in the front end directly.

I am wondering is there any difference between-

--creating aggreated table in script and then using those generated fields in the UI objects.

--using Aggr(to aggregate the data across the necessary dimensions) in the UI directly.

Re: Aggr() in UI vs Group by in script

swuehl — Fri, 02 May 2014 16:16:21 GMT

Script aggregations are not sensitive to selections.

Re: Aggr() in UI vs Group by in script

hic — Fri, 02 May 2014 16:38:38 GMT

If you want static aggregations, you should do it in the script. That will save CPU time since less needs to be calculated when the user clicks. However, as Stefan points out, you usually you want the aggregation to be dynamic; to be sensitive to selections; and then you should not pre-aggregate in the script. You should do it in the UI.

Finally, a script aggregation, e.g. a Group By with a Sum(...), corresponds to a chart where the dimension defines the grouping symbol and the expression contains an aggregation function, e.g. the Sum(...).

The Aggr() function is something different, and has in principle nothing to do with the aggregation itself. It is merely a function used to define the loop in a nested aggregation. It has no counterpart in the script. Hence, you should normally not use Aggr() to replace a script aggregation.

HIC

Re: Aggr() in UI vs Group by in script

surajap123 — Tue, 06 May 2014 08:41:53 GMT

Hi HIC,

Thanks a lot for the information.

I understand that Aggr function has no counterpart in script. However I am thinking to use Aggr function, instead of group by in script. So i will be using the below expression.

Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate)

My old script has the following code to create aggregation fields-->

InvoiceTemp:

LOAD

InvoiceLineId,

InvoiceType,

((Amount / Discount) / (INTERVAL(DATE(InvoiceToDate) - (InvoiceFromDate),'D')+1)) AS InvoiceAmount,

Date(InvoiceFromDate + IterNo() -1 ) AS InvoiceDate

Resident Invoicetable

WHILE IterNo() <= Date(InvoiceToDate) - InvoiceFromDate +1;

Invoice:

LOAD

InvoiceLineId,

InvoiceType,

InvoiceDate,

SUM(InvoiceAmount) AS InvoiceAmount

RESIDENT InvoiceTemp

GROUP BY InvoiceDate,InvoiceType, InvoiceLineId;

DROP table InvoiceTemp;

-------

I just want to confirm, will i get the same result with just using below expression, compare to using SUM(InvoiceAmount) in front end, along with above script in the dashboard??

Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate)

Re: Aggr() in UI vs Group by in script

tresB — Tue, 06 May 2014 08:50:08 GMT

The order of fields mentioned in group By and Aggr() matters. That means you have to realize that :

Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate) and

Aggr(SUM(InvoiceAmount),InvoiceDate,InvoiceType,,intInvoiceLineId) are NOT same.

Not only that, I would like to mention one more point: QV does this aggregation in the front end with use of dimensions in charts where you might not have to use this AGGR().

Re: Aggr() in UI vs Group by in script

Tue, 06 May 2014 08:57:48 GMT

Hi,

for me it looks like your results should be the same, but ....

remamber, that in fact aggr() produces virtual table.

So, your expression:

Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate)

will give you in result for each combination of your dimensions result of your expression.

If for example you will vave 2 different lineID and 2 type and 2 Date, you will get 6 rows from your aggr().

What will you see in your chart?

If you have for example no dimension in this chart, you will see nothing. And it is difference, because in this case your SUM(InvoiceAmount) give you result.

And i think, in this case even if you dont have groupby in your script, SUM(InvoiceAmount) will still give you the same result.

But lets look like on another case.

Lets say, that you would like in your old application calculate: avg(InvoiceAmount)

In this case new expression:

avg(Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate)) should give the same result.

regards

Darek

Re: Aggr() in UI vs Group by in script

surajap123 — Tue, 06 May 2014 09:16:14 GMT

Thanks all..

I finally assume that i can use aggr() can be used to fulfill my requirement, without the need of group by in script..