topic sum if during load in QlikView

sum if during load

Tue, 05 May 2009 15:13:27 GMT

Hi,

is it possible to sum during Load e.g.

IF (AMOUNT_TYPE='<100', SUM(AMOUNT) AS XYZ)

SUM (AMOUNT) AS AMOUNT_AGGR

Is it possible to define variables with values of loaded tables?

e.g. SET myvar = SUM (AMOUNT)

Thanks

sum if during load

Anonymous — Tue, 05 May 2009 16:11:50 GMT

Hi,

You are able to do sums in load statements at least like the second expression:

SUM (AMOUNT) AS AMOUNT_AGGR

In order for the aggregation to work you need to use the Group By clause and group by all fields that aren't part of the aggregation.

For example, imagine the following table with 2 columns, Customer and Amount:

A	3
A	6
B	5

In this case you'd have to write:

Load

Customer,

SUM (AMOUNT) AS AMOUNT_AGGR

From xxx

Group By Customer;

This way the two rows related to customer A are grouped into one with the AMOUNT_AGGR value of 9.

sum if during load

rbecher — Tue, 05 May 2009 16:53:15 GMT

Hi Aloah,

this is a simple example:

testdata:
LOAD * INLINE [
F1, F2
A, 57
B, 83
C, 24
D, 119
];

testsum:
LOAD
sum(if(F1>'A',F2,0)) as sum_of_F2
RESIDENT testdata;

You can see the result in the attached qvw file.

Ralf

sum if during load

rbecher — Tue, 05 May 2009 17:50:33 GMT

I forgot the second part:

let vSum = peek('sum_of_F2');

sum if during load

Tue, 05 May 2009 22:01:07 GMT

Thanks. Works perfect.