Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
What a easy reply Gysbert.
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?
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