topic Variables in Load Scrip - How to do it in Qlik Sense in App Development

Variables in Load Scrip - How to do it in Qlik Sense

venturahl — Thu, 31 Mar 2016 15:51:45 GMT

Hello,

I'm having a problem when I try to create a variable with calculations and set anaysis in load script.

I've an huge expression (250 lines and 8000 characters) that I can't finished it because i've no space to do it and i need to do it in load script.

How can I do it in Qlik Sense?

My expression in front end is:

Sum({<DateNum={">=$(=$(vInitialDate))<=$(=$(vFinalDate))"},COD_RUB_DR = {'RES_SUBEXP'}>}MTR_SALDO_FINAL)

where

$(vInitialDate) ---> SET vInitialDate= num(MakeDate(year(today()),1,1));

$(vFinalDate) ---> SET vFinalDate= num(MonthEnd(AddMonths(Today(),-1)));

I've done in load Script like this:

SET vTeste = '(Sum({<DateNum={">=$((vDataIniSemSelec))<=$((vDataFimMesAntSemSelect))"}, COD_RUBRICA_DR = {"RES_SUBEXP"}>}MTR_SALDO_FINAL))';

Can anyone help me with this??

Best regards,

Helga

Re: Variables in Load Scrip - How to do it in Qlik Sense

swuehl — Thu, 31 Mar 2016 16:18:01 GMT

I believe the dollar sign expansion will cause QS to immediately replace the variables in your script, which is not what you want.

You would need to use a kind of replace approach or a string concatenation or better use an external excel file to store your variables.

edit:

For example:

qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/

Re: Variables in Load Scrip - How to do it in Qlik Sense

venturahl — Thu, 31 Mar 2016 16:45:28 GMT

Hello again,

I've try it and doesn't work =(

Re: Variables in Load Scrip - How to do it in Qlik Sense

swuehl — Thu, 31 Mar 2016 16:57:54 GMT

You have tried exactely what?

Re: Variables in Load Scrip - How to do it in Qlik Sense

ziadm — Thu, 31 Mar 2016 17:03:41 GMT

Hi Try to enclose the variables with a single quote character

Sum({<DateNum={">=$(='$(vInitialDate)')<=$(='$(vFinalDate)')"},COD_RUB_DR = {'RES_SUBEXP'}>}MTR_SALDO_FINAL)

Also

use Let instead of Set

Let vInitialDate= num(MakeDate(year(today()),1,1));

Hope this work ..

Re: Variables in Load Scrip - How to do it in Qlik Sense

venturahl — Fri, 01 Apr 2016 17:43:11 GMT

Hello again...

I've try lots of things and the result is the same.... nothing...

This result ignores the set analysis and returns me the sum of the metric..

any sugestion?

Thanks

Re: Variables in Load Scrip - How to do it in Qlik Sense

venturahl — Fri, 08 Apr 2016 11:25:57 GMT

Hello,

The solution is:

You Cannot use single quotes ('') and quotation marks ("") in the set analysis!

LET vRES_VESRPRS_BRT ='(Sum({<Year=, Month=, DateNum={">=' & chr(36) & '(=' & chr(36) & '(vInitialDate))<=' & chr(36) & '(=' & chr(36) & '(vFinalDate))"}, COD_RUB_DR = {RES_VESRPRS_BRT} >}MTR_SALDO_FINAL))';