Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alaope2q
New Contributor II

use STORE to export variables or set analysis in script

I will like to export results from Chart (Table) in Qlik to text format. These are from calculations embedded into pivot table with more than 3 million rows. When I export data directly in Qlik it gets truncated but the I need the data in text format with all the results. I used store and it worked for simple dimensions and measures but when I try a calculation with a variable it doesn´t work.

For example:

LET vMARCH) = 'if(aggr(if((Sum({$}[Mar 17])-Sum({$}[Sum_MAR_17]))<>0 ,1,0), referenceID ) = 1, referenceID)';

Now I call and store the variable

store $(vMARCH),Sum_MAR_17, Mar17 from LM_DE into lib://Dario/reference_MAR.txt (txt);

but it wouldn’t work.

Can anybody help or suggest how to deal with this

11 Replies
rohitk1609
Valued Contributor II

Re: use STORE to export variables or set analysis in script

Hi,

Aggr is a front end function which you can use in calculated dimension and measure. Second what you are trying to do to use AGGR at script level. have you ever check what value is coming when you are expanding your variable $(vMARCH) ?

alaope2q
New Contributor II

Re: use STORE to export variables or set analysis in script

I already used this in a table and it works flawlessly but I cannot export the results because I have more than 1 million rows.

omarbensalem
Esteemed Contributor

Re: use STORE to export variables or set analysis in script

aggr is a front end function; to use it in the script, you replace it with group by.

alaope2q
New Contributor II

Re: use STORE to export variables or set analysis in script

Thanks for the tips, do you think if i replace with group by, STORE function will work for the variable?

alaope2q
New Contributor II

Re: use STORE to export variables or set analysis in script

Now i used this


LET vMARCH) = '(if((Sum({$}[Mar 17])-Sum({$}[Sum_MAR_17]))<>0 ,1,0), referenceID ) = 1,

group by (referenceID))';


but it still doesnt work

omarbensalem
Esteemed Contributor

Re: use STORE to export variables or set analysis in script

Try something like this maybe:

load referenceID , if(YourSum<>0,1,0) as Somme;

load

referenceID ,

(Sum([Mar 17])-Sum([Sum_MAR_17])) as YourSum

group by referenceID ;

let vMARCH=if(Somme=1, referenceID);

alaope2q
New Contributor II

Re: use STORE to export variables or set analysis in script

Thanks for the efffort Omar. I tried this but it still doesn¨t work .

omarbensalem
Esteemed Contributor

Re: use STORE to export variables or set analysis in script

Can you share the error message?

a screen shot maybe?

alaope2q
New Contributor II

Re: use STORE to export variables or set analysis in script

The following error occurred:

Field 'Sum_MAR_17' not found

The error occurred here:

?

"Sum_MAR_17" could not be found even though i used this in the pivot table.


I also tried this

LOAD referenceID , if([Mar 17] - Sum_MAR_17) <>0,1,0) as Somme

group by referenceID;

let vMARCH=if(Somme=1, referenceID);

but no results