sum if during load

[#1]

Hi,

is it possible to sum during Load e.g.

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

or

SUM (AMOUNT) AS AMOUNT_AGGR

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

e.g. SET myvar = SUM (AMOUNT)

Thanks

[Anonymous]

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.

[rbecher]

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

[rbecher]

I forgot the second part:

let vSum = peek('sum_of_F2');

[#5]

Thanks. Works perfect.