Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sum if during load

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

4 Replies
jsn
Master
Master

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:

A3
A6
B5


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
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
rbecher
MVP & Luminary
MVP & Luminary

I forgot the second part:

let vSum = peek('sum_of_F2');

Vizlib Head of R&D
Not applicable
Author

Thanks. Works perfect.