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

[thanhphongle]

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();