topic Re: Date Variable in Where clause in load script in QlikView

Date Variable in Where clause in load script

Fri, 28 Feb 2014 09:13:44 GMT

Dear All,

I need to put a where clause in load script.

Example :

LET vCutoffDate = makedate(2013);

LOAD [Invoice Date], [Standard Invoice Qty], [Invoice Amount], [Cost Value], [Invoice #]

FROM

Sales.QVD (qvd) WHERE [Invoice Date] >= vCutoffDate ;

How to achieve this ? Your help is badly needed.

Note : I do not want to change the SQL which is used to get data from DB using Select statement.

Regards,

Sahas

Re: Date Variable in Where clause in load script

Jason_Michaelides — Fri, 28 Feb 2014 09:22:05 GMT

Hi Sahas,

I think you're pretty much there, you just need dollar-expansion around the variable:

LET vCutoffDate = makedate(2013,10,30);

LOAD [Invoice Date], [Standard Invoice Qty], [Invoice Amount], [Cost Value], [Invoice #]

FROM

Sales.QVD (qvd) WHERE [Invoice Date] >= '$(vCutoffDate)' ;

However, using the WHERE clause here will break the optimised QVD load. You may want to think about pre-loading all possible dates greater than your variable, then using WHERE EXISTS([Invoice Date]) to keep the load optimised.

Hope this helps,

Jason

Re: Date Variable in Where clause in load script

MK_QSL — Fri, 28 Feb 2014 09:22:10 GMT

Sales.QVD (qvd) WHERE [Invoice Date] >= '$(vCutoffDate)' ;

Re: Date Variable in Where clause in load script

Fri, 28 Feb 2014 09:24:15 GMT

try this in load

WHERE [Invoice Date] >= $(vCutoffDate);

Re: Date Variable in Where clause in load script

Fri, 28 Feb 2014 09:28:55 GMT

Dear All,

Thanks for your quick reply.