Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

LOAD, dynamic, variable to table

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?

2 Replies
richard_pearce6
Not applicable

Re: LOAD, dynamic, variable to table

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

filosofo
Not applicable

Re: LOAD, dynamic, variable to table

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.