topic Re: calculated SET variable based on fields in QlikView

calculated SET variable based on fields

Thu, 02 May 2013 09:39:09 GMT

Hi,

I have a (beginner) question :

Let's say I load 2 fields from a xls table

LOAD

[field A] as A,

[field B] as B

FROM xxxxx

Field A and B contains random numbers range 1 - 100.

Now I would like to have a variable vCal which calculates the difference.

SET vCAL = Sum($1 A) - Sum($1 B)

Now I'm a bit confused on where I need to place this SET variable. When I put it with the other standard SET variables at the beginning of the script it comes before the LOAD so it doesn't recognize the values

You cannot place a SET within the LOAD don't you? Or is it possible to do the following :

LOAD

field A as A,

field B as B

Cal = [Field A] - [Field B]

FROM xxxxx

Re: calculated SET variable based on fields

Gysbert_Wassenaar — Thu, 02 May 2013 09:44:57 GMT

You can do the calculations in the load by creating a new field that subtracts the two fields:

LOAD

[field A] as A,

[field B] as B,

[field A] - [field B] as Cal

FROM xxxxx

The set statement SET vCAL = Sum($1 A) - Sum($1 B) creates a variable that contains an expression. That expression doesn't make sense though. Sum(A)-Sum(B) would make sense. You could such a variable in a chart objects. It would still be meaningless in the script though since it will miss the context that a chart would give it, i.e. dimensions to aggregate over.

Re: calculated SET variable based on fields

sujeetsingh — Thu, 02 May 2013 11:12:17 GMT

What a easy reply Gysbert.

Re: calculated SET variable based on fields

Thu, 23 Jan 2014 13:27:43 GMT

Hello

I have a similar problem to this, so i wonder if you can help.

I have the following table:

Limits:

Load

max(Sales) as maxSale,

min(Sales) as minSale

resident Sales;

Now I need to turn maxSale and minSale to variables to use in another Load statement which follows.

Tried it the following:

LET vMaxSaleAmount = peek(maxSale);

LET vMinSaleAmount = peek(minSale);

Data:

Load $(vMinSaleAmount)-1+iterno() as Value

autogenerate 1 while iterno() <= $(vMaxSaleAmount)+1 - $(vMinSaleAmount);

Wwhen reloading, there variable is not found in the 'Data' table. Can you help?

Re: calculated SET variable based on fields

Thu, 23 Jan 2014 13:48:43 GMT

Hi Zama

You need quotes in PEEK:

LET vMaxSaleAmount = peek('maxSale');

BTW, it would be better to open a new thread instead of reusing seven months old discussion

Lukasz