calculated SET variable based on fields

[#1]

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

[Gysbert_Wassenaar]

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.

[sujeetsingh]

What a easy reply Gysbert.

[#4]

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?

[#5]

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