topic Using a variable for calculation in the script in QlikView

Using a variable for calculation in the script

thanhphongle — Mon, 18 Sep 2017 10:39:54 GMT

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

Re: Using a variable for calculation in the script

prieper — Mon, 18 Sep 2017 10:44:38 GMT

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;

Re: Using a variable for calculation in the script

effinty2112 — Mon, 18 Sep 2017 10:46:57 GMT

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

Re: Using a variable for calculation in the script

thanhphongle — Mon, 18 Sep 2017 10:50:19 GMT

Thank you for the quick answer

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

Re: Using a variable for calculation in the script

vinayakg — Mon, 18 Sep 2017 11:09:00 GMT

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

Re: Using a variable for calculation in the script

effinty2112 — Mon, 18 Sep 2017 11:10:14 GMT

Hi Thanh,

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

Kind regards

Andrew

Re: Using a variable for calculation in the script

thanhphongle — Mon, 18 Sep 2017 11:36:28 GMT

It works !

Thank you very much!

Re: Using a variable for calculation in the script

thanhphongle — Tue, 19 Sep 2017 10:39:38 GMT

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?

Re: Using a variable for calculation in the script

prieper — Tue, 19 Sep 2017 11:45:05 GMT

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'))

Re: Using a variable for calculation in the script

thanhphongle — Tue, 19 Sep 2017 11:55:39 GMT

actually I defined a variable v_target_date=today()

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

Re: Using a variable for calculation in the script

thanhphongle — Tue, 19 Sep 2017 11:59:24 GMT

See here my current script

Data:

LOAD *,

//((Year(contract_end_date_cur)-year($(v_target_date)))*12+month(contract_end_date_cur)-month($(v_target_date))) as contract_term_remain;;

Directory;

LOAD main_unique_id,

bp_customer,

bp_contract_partner,

asset_model,

fi_rate_cur,

contract_start_date,

contract_end_date_reg,

contract_end_date_cur

FROM

lease.one_import.xlsx

(ooxml, embedded labels, table is main)

where fi_rate_cur>0;

let v_target_date = today();