Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pascos88
Contributor

Use of a variable in the load script

Hello,

I create a variable in the script V_DIFF:

Trace ***** Dimension: min_conf_last_year *****;

min_conf_last_year:

Load

min(PRT_CONFIRMDATE) as minLastConf;

SQL Select * from "APP_CUR"."SUR_PARTICIPATION_T"

where CAM_ID = $(v_Last_Year);

Trace ***** Dimension: min_conf_this_year *****;

min_conf_this_year:

Load

min(PRT_CONFIRMDATE) as minthisConf;

SQL Select * from "APP_CUR"."SUR_PARTICIPATION_T"

where CAM_ID = $(v_This_Year);

SET V_DIFF = ROUND(minthisConf-minLastConf)


Now I want use this variable to calculate a sum in  another table, but I get error:


Trace ***** Dimension: Partecipant *****;

Partecipant:

if( Year(PRT_CONFIRMDATE) = '2016', date(floor(PRT_CONFIRMDATE + $(V_DIFF) ),'DD.MM.YYYY'), date(floor(PRT_CONFIRMDATE),'DD.MM.YYYY')) as DataUpd_Conf,

;

  

SQL

Select * from ...

Why I cannot use the valiable like this?

How I can do ?

Thanks



9 Replies

Re: Use of a variable in the load script

have you tried using variable between single quotes?

'$(YourVariable)'

pascos88
Contributor

Re: Use of a variable in the load script

Thanks for the aswere... looks that in the variable there is no the calculation but the string ROUND(minthisConf-minLastConf).. How it is possible? I would like to have the value inside...

Re: Use of a variable in the load script

Set V_DIFF = Round(Peek('minthisConf')-Peek('minLastConf'))

pascos88
Contributor

Re: Use of a variable in the load script

nope, doest't works.. In the variable there is always the value as string.. in this case Round(Peek('minthisConf')-Peek('minLastConf'))


if I try to show the value as $(V_diff) I get an invalid dimension

Re: Use of a variable in the load script

What is the value of

minthisConf

and

minLastConf

pascos88
Contributor

Re: Use of a variable in the load script

It is a date

Re: Use of a variable in the load script

What is the format of that date?

What is the format of below variable in your script?

SET DateFormat='DD/MM/YYYY';

pascos88
Contributor

Re: Use of a variable in the load script

HI guys sorry still this problem.. Some helps? thanks

Re: Use of a variable in the load script

Try it with:

LET V_DIFF = Round(Peek('minthisConf', 0, 'min_conf_this_year') -

                                    Peek('minLastConf', 0, 'min_conf_last_year'));

There is no need to transfer the calculation as expression within the further load-statement and to evaluate for each record because it won't change in it. Therefore you could use LET to get the result of this calculation in the variable. Further are the second/third parameter for peek() added because by evaluating a peek-function outside from a load they are mandatory.

If this don't work take a look on the content of your created min-tables (without a drop-statement they would remain within the datamodel - a look within the tableviewer should be enough by just one record) and on the content of your variable, for example with: Trace $(V_DIFF);.

Further if your dates are really dates and no timestamps you could relinquish of the round/floor-functions and for the min-loads you don't need all fields from the database which a SELECT * FROM would query.

- Marcus

Community Browser