Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Trying to simulate a montly report. To do that I need to save aggregated data in a table. No problem saving vVar, but I want to save the calculated value - in this case 830.
LET vVar='sum({<region={1}>}sales)';
sales:
LOAD * Inline [
region,sales
1,200
1,630
2,300];
x:
LOAD $(vVar) AS Value AUTOGENERATE(1);
Any suggestions?
Hi Thomas,
You can't use set analysis in a script, you could generate aggr tables and peek the results; something like this:
sales:
LOAD * Inline [
region,sales
1,200
1,630
2,300];
Sales_Sum:
NoConcatenate LOAD
sum(sales) as sum_sales
Resident sales_temp
where region=1
group by region;
LET vVar=Peek('sum_sales',0,'Sales_Sum');
x:
LOAD $(vVar) AS Value AUTOGENERATE(1);
You could use varibles / loop / curser tables to define the field you're checking and the field you're grouping by (set) if your requirement is more complex than your example.....
Hope this help....
Hi, another way to do it. Less suitable if you have a lot of regions, too much (nested) if's, but it does the job here. Less SQL, more formulas. Also gives
you the possibilty to do any calc on the sales figures on scriptlevel and then have it available as flat records in the new inline table. Depends on your plans. Might help...
Let c=0;
Let d=0;
sales:
LOAD * Inline [
region,sales
1,200
1,630
2,300];
For i=0 to NoOfRows('sales')-1;
Let a=Peek('region', $(i), 'sales');
Let b=Peek('sales', $(i), 'sales');
If $(a)=1 then
Let c=$(c)+$(b);
Endif
If $(a)=2 then
Let d=$(d)+$(b);
Endif
Next i;
x:
LOAD * Inline [
Reg1_Tot, Reg2_Tot
$(c), $(d)
];
Regards.