Using a variable for calculation in the script

[thanhphongle]

Hello Community

can anyone show me how I can use variables which I defined in the script for calculation and creating a new attribut?

Example:

Directory;

LOAD

     product,

     start_date

     end_date

FROM

sales_import.xlsx

(ooxml, embedded labels, table is main)

let v_target_date=today();

My new variable is target_date. This target_date can be flexible. It must not be the current date. It can be changed in the tool.

Now I want to create a new attribut called remaining_term which shows how many months are remained till reaching the end_date

I edit the script

Data:

LOAD *,

     ((Year(end_date)-year(v_target_date))*12+month(end_date)-month(v_target_date)) as remaining_term;

Directory;

LOAD

     product,

     start_date

     end_date

FROM

sales_import.xlsx

(ooxml, embedded labels, table is main)

let v_target_date=today();

Unfortunetely it is not working. I think it can't find the defined variable v_target_date

[prieper]

Please wrap the variable into $(..),

Data:

LOAD *,

     ((Year(end_date)-year($(v_target_date)))*12+month(end_date)-month($(v_target_date))) as remaining_term;

[effinty2112 · Accepted solution]

Hi Thanh,

                    For QlikView you use the variable you need to place the variable name inside brackets preceded by a dollar sign:

Data:

LOAD *,

    ((Year(end_date)-year($(v_target_date)))*12+month(end_date)-month($(v_target_date))) as remaining_term;

This is known as dollar sign expansion, sometimes shortened to DSE. Without DSE QlikView would be looking for a field called v_target_date instead of the value of the variable.

Kind regards

Andrew

[thanhphongle]

Thank you for the quick answer

But does QlikView consider that my variable v_target_date = today()  ?

[vinayakg]

Hi Thahn,

For using Variables in the script we need to mention the $(Variable_Name),So

Just Try This, It may Work For u...

Data;

LOAD

     product,

     start_date

     end_date

FROM

sales_import.xlsx

(ooxml, embedded labels, table is main)

let v_target_date=today();

load *,

((Year($(end_date))-year($(v_target_date)))*12+month($(end_date))-month($(v_target_date))) as remaining_months

Resident Data;

Regards,

Vinayak

[effinty2112]

Hi Thanh,

Test it to see. Make a textbox with the expression =$(v_target_date)

Kind regards

Andrew

[thanhphongle]

It works !

Thank you very much!

[thanhphongle]

For the upper example it somehow works. But if I try to use the attributes from my database with exactly the same expression it give it returns me this error.

Error in expression:

')' expected

Data:

LOAD *,

    ((Year(contract_end_date_cur)-year(19.09.2017))*12+month(contract_end_date_cur)-month(19.09.2017))

Can you help me out here?

[prieper]

Think that you should not use the formatted entry for a date "19.09.2017" but please use a numeric expression instead:

-YEAR(DATE#(20170919, 'YYYYMMDD'))

[thanhphongle]

actually I defined a variable v_target_date=today()

QV automatically set it to the actual date when I load the script