3

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.

dorianpc
  • 319
  • 2
  • 5
  • 10

1 Answers1

4

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;
i_saw_drones
  • 3,486
  • 1
  • 31
  • 50
  • I had no idea QV did not allow this while assigning a variable in a script, but your workaround worked perfect for me. Thanks for your help. – dorianpc Aug 26 '14 at 23:51
  • QV does allow you to create variables in the same way as you did in your script, however, they cannot be used in subsequent script statements. However, QV allows you to use those variables in the front-end in tables and charts as an expression which is dynamically calculated. HTH :) – i_saw_drones Aug 27 '14 at 08:42