Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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.