Creating variable during load script in Qlikview

Question

I am trying to set a variable in my load script using a previous loaded table, for example:

Data:
load * inline
[
Year, Store, Revenue
2008, Amsterdam, 100
2009, Amsterdam, 100
2010, Amsterdam, 100
2008, Rotterdam, 200
2009, Rotterdam, 200
2010, Rotterdam, 200
2008, The Hague, 300
2009, The Hague, 300
2010, The Hague, 300
];

Let vRevenueTotal  = Sum(Revenue);

But I am getting Null for vRevenueTotal. I am new to Qlikview, what am I doing wrong here? Thanks in advance.

Answer 1

Unfortunately you cannot directly use Sum in a variable this way since QlikView doesn't know how to sum the field (i.e. with respect to other fields), hence it returns null.

One way you could achieve the result you require is to create a temporary table that does the summation for you and then peek at the result in the table and assign it to your variable.

In the below script example, a temporary "summation" table was created which uses a RESIDENT load to take the data from an existing table (in this case Data) and then sums the Revenue field into a new field in this table.

We can then use the peek function to obtain the first row of the temporary table (SumData) and return the value of the RevenueSum field which is then assigned to the variable vRevenueTotal, which then contains the value 1800 as required.

We then drop the temporary table to keep things tidy.

Data:
load * inline
[
Year, Store, Revenue
2008, Amsterdam, 100
2009, Amsterdam, 100
2010, Amsterdam, 100
2008, Rotterdam, 200
2009, Rotterdam, 200
2010, Rotterdam, 200
2008, The Hague, 300
2009, The Hague, 300
2010, The Hague, 300
];

SumData:
LOAD
    sum(Revenue) as RevenueSum
RESIDENT Data;

Let vRevenueTotal  = peek('RevenueSum',0,'SumData');

DROP TABLE SumData;