Use of a variable in the load script

[pascos88]

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

[MK_QSL]

have you tried using variable between single quotes?

'$(YourVariable)'

[pascos88]

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

[MK_QSL]

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

[pascos88]

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

[MK_QSL]

What is the value of

minthisConf

and

minLastConf

[pascos88]

It is a date

[MK_QSL]

What is the format of that date?

What is the format of below variable in your script?

SET DateFormat='DD/MM/YYYY';

[pascos88]

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

[marcus_sommer]

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