Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
I forgot the second part:
let vSum = peek('sum_of_F2');
Thanks. Works perfect.