Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to use variables in the load script to then use in the where clause to limit data loaded into Qlikview?
Depending on the current date I would like to create a variable that is used in the SQL select statement to determine what data gets loaded.
For example in SQL Server 2012 I can write:
DECLARE @fiscalyear varchar(30)
DECLARE @lastfiscalyear varchar(30)
IF GETDATE()>=CAST(CONVERT(VARCHAR(10),'07/01/' + DATENAME(YEAR,GETDATE())) as DATE)
BEGIN
SET @fiscalyear = SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,1,GETDATE())),3,2) + ' Fiscal Year'
SET @lastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year'
END
ELSE
BEGIN
SET @fiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year'
SET @lastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-2,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + ' Fiscal Year'
END
I then have my SQL Statement here using @fiscalyear and @lastfiscalyear in my where clause, but I can't seem to figure out how this would work in the Qlikview load script.
Thank you Henric for all your help. I finally go it to work.
Once I finally realized what it was doing, it came together.
What I realized is that Qlikview wasn't calculating the variable and then putting it in the SQL code to then pass to SQL Server, it was just passing the exact syntax that was in the variable and was expecting SQL Server to calculate, so I changed the variables as if I was to write them in SQL Server.
Example:
This:
IF Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD') THEN
LET vfiscalyear = Mid(Date(Today(),'YYYY'),3,2) + Mid(Date(AddYears(Today(),1),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
ELSE
LET vfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-2),'YYYY'),3,2) + Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + ' Fiscal Year' ;
ENDIF
Should be this:
IF Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD') THEN
LET vfiscalyear = SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,1,GETDATE())),3,2) + ' Fiscal Year';
LET vlastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year';
ELSE
LET vfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year';
LET vlastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-2,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + ' Fiscal Year' ;
ENDIF
Thanks again.
Yes, it is possible. If the field Year in the DB is an integer, you can write:
Set vYear = 2009 ;
SQL SELECT * FROM Orders WHERE Year = $(vYear) ;
In your case, your fiscal years seem to have a different format, so the where clause would probably need to be a string comparison:
Let vFiscalYear = < proper definition> ;
SQL SELECT * FROM Orders WHERE FiscalYear = '$(vFiscalYear)' ;
HIC
So, I've worked my way through all the errors this was throwing me and I'm still stuck. As Henric has mentioned I changed the code a little, but I still get an error saying: "Invalid column name 'vlastfiscalyear'"
Here's what I have so far:
IF Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD') THEN
LET vfiscalyear = Mid(Date(Today(),'YYYY'),3,2) + Mid(Date(AddYears(Today(),1),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
ELSE
LET vfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-2),'YYYY'),3,2) + Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + ' Fiscal Year' ;
ENDIF
When I change the 'v' to '@' like SQL Server I get the error "Must declare the scalar variable "@lastfiscalyear" and I'm sure once it gets past this one it will require it for the next. How would I declare the variables or get the 'v' to work?
That error shows that the SQL Server interprets the variable as a DB field, which it shouldn't. Check that you use single quotes around the variable expansion (or no quotes, if it is a number).
You should NOT change the 'v' to a '@' - it is not a SQL variable. It is a QlikView variable.
Run the script in the debugger, and you should see something like the image below:
HIC
Thanks Henric this helped, in that it set the variables and now my script passes to the SQL Server, but I'm still running into the issue where it won't calculate my variables. For example as I step through the script the variable ends up being "Mid(Date(Today(),'YYYY'),3,2) + Mid(Date(AddYears(Today(),1),'YYYY'),3,2) + ' Fiscal Year'" instead of "1213 Fiscal Year". How do I get it to calculate the variable correctly.
Not sure, but it could be that you use + as string concatenation operator. This does not work. Use & instead.
Further, maybe some date handling could be made simper. For instance you use
Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD')
when you perhaps should use
Today()>=MakeDate(Year(Today()),01,07)
instead.
And
Mid(Date(Today(),'YYYY'),3,2) + Mid(Date(AddYears(Today(),1),'YYYY'),3,2)
can be replaced with
Date(Today(),'YY') & Date(AddYears(Today(),1),'YY')
HIC
Thank you Henric for all your help. I finally go it to work.
Once I finally realized what it was doing, it came together.
What I realized is that Qlikview wasn't calculating the variable and then putting it in the SQL code to then pass to SQL Server, it was just passing the exact syntax that was in the variable and was expecting SQL Server to calculate, so I changed the variables as if I was to write them in SQL Server.
Example:
This:
IF Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD') THEN
LET vfiscalyear = Mid(Date(Today(),'YYYY'),3,2) + Mid(Date(AddYears(Today(),1),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
ELSE
LET vfiscalyear = Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + Mid(Date(Today(),'YYYY'),3,2) + ' Fiscal Year';
LET vlastfiscalyear = Mid(Date(AddYears(Today(),-2),'YYYY'),3,2) + Mid(Date(AddYears(Today(),-1),'YYYY'),3,2) + ' Fiscal Year' ;
ENDIF
Should be this:
IF Today()>=Date('07/01/' + Date(Today(),'YYYY'),'YYYY-MM-DD') THEN
LET vfiscalyear = SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,1,GETDATE())),3,2) + ' Fiscal Year';
LET vlastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year';
ELSE
LET vfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,GETDATE()),3,2) + ' Fiscal Year';
LET vlastfiscalyear = SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-2,GETDATE())),3,2) + SUBSTRING(DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())),3,2) + ' Fiscal Year' ;
ENDIF
Thanks again.