topic Re: Load script variables in QlikView

Load script variables

Thu, 08 Aug 2013 17:29:05 GMT

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.

Re: Load script variables

hic — Fri, 09 Aug 2013 07:23:39 GMT

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

Re: Load script variables

Fri, 09 Aug 2013 14:29:56 GMT

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?

Re: Load script variables

hic — Fri, 09 Aug 2013 14:43:41 GMT

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:

Execute step-wise (red)
Check that the script looks OK (green). This is what QlikView sees.
Check that the variable expansion has been made correctly (blue). This is what is sent to the SQL Server.

HIC

Re: Load script variables

Fri, 09 Aug 2013 15:03:52 GMT

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.

Re: Load script variables

hic — Fri, 09 Aug 2013 15:20:10 GMT

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

Re: Load script variables

Fri, 09 Aug 2013 15:50:23 GMT

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.