Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
Thank you for the quick answer
But does QlikView consider that my variable v_target_date = today() ?
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
Hi Thanh,
Test it to see. Make a textbox with the expression =$(v_target_date)
Kind regards
Andrew
It works !
Thank you very much!
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?
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'))
actually I defined a variable v_target_date=today()
QV automatically set it to the actual date when I load the script