topic LOAD, dynamic, variable to table in QlikView

LOAD, dynamic, variable to table

Fri, 24 Aug 2012 12:59:45 GMT

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?

Re: LOAD, dynamic, variable to table

richard_pearce6 — Fri, 24 Aug 2012 14:32:50 GMT

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....

Re: LOAD, dynamic, variable to table

Anonymous — Fri, 24 Aug 2012 15:32:42 GMT

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.