topic Assign variable with select statement in QlikView

Assign variable with select statement

Wed, 25 Nov 2020 16:16:04 GMT

Im having problems with this.

I have this qlikview script which is run on the 15th of every month. This script intends to automatically take the last date of the previous month in the format of 'YYYY-MM-DD'. For example today is 15th January 2017. I will run my script and it will give me a date of 31th December 2016 in the format 2016-12-31. If it is 15th December 2016, I will get 30th November 2016 in the format 2016-11-30.

I want to set the date retrieved as a variable in the format yyyy-mm-dd to be used in another query.

Basically I tried

SET @vLastDate = DATE_FORMAT(select last_day(curdate() - INTERVAL 1 MONTH), '%Y-%m-%d');

When I checked the variable values in settings, I get

value is DATE_FORMAT(select last_day(curdate() - INTERVAL 1 MONTH), '%Y-%m-%d')

but what I want is the last date value itself.

I am doing this in qlikview. Please help me identify my problem thank you.

Re: Assign variable with select statement

johnw — Thu, 12 Jan 2017 17:34:23 GMT

If you want the date value itself in a variable in QlikView, you would need to use QlikView functions, not whatever you would use in the select statement if you were coding it there instead. Also a LET instead of SET, because LET evaluates the expression, while SET just takes it literally. Maybe this.

LET @vLastDate = date(monthstart(today())-1,'YYYY-MM-DD');

Re: Assign variable with select statement

rahulpawarb — Fri, 13 Jan 2017 12:36:04 GMT

I am 100% agree with John. if the variable needs to calculate values based on some expression then use LET or else you can use SET assign hard code value to it. Also, refer the QlikView function(s) at variable declaration/assignment. If you still want to go ahead with SQL compliant functions then load the SQL expression into a table and assign it to a variable using PEEK function.

Hope this makes sense.

Regards!

Rahul