topic Re: Load only last 8 weeks data in QlikView

Load only last 8 weeks data

hopkinsc — Thu, 20 Oct 2011 08:54:57 GMT

Hi, i have an application that is too big for our current server. I want to try and only load in the last 8 weeks worth of data but am unsure how to do this in the load script.

Can anyone help please?

I have attached my load script

Re: Load only last 8 weeks data

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 09:03:27 GMT

Hi Chris,

You need to add a WHERE clause to each table you want to reduce data, for example

LOAD StoreNumber, 
     TransId, 
     TransStartDate, 
     TransStartTime, 
     EmployeeId, 
     TillNo, 
     TransStatus, 
     OverallDiscount, 
     Nolines, 
     TradingDate, 
     ZreadRef, 
     TrainingMode, 
     TrainerId, 
     %LkStoreTransID, 
     %lkStoreEmployee
FROM $(cFileName) (qvd)
WHERE Date(TransStartDate) >= Date(Today() -56);

You can try a debug loading limiting the load to the first X number of rows.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Load only last 8 weeks data

hopkinsc — Thu, 20 Oct 2011 09:09:56 GMT

Hi Miguel,

Thanks for your help again, is there a way of obtaining this but by still loading in optimized?

Thanks

Chris

Re: Load only last 8 weeks data

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 09:20:19 GMT

Hi Chris,

You could create select from source so instead of having thw WHERE in the LOAD part, you have it in the SQL part, using the DBM dates functions. The result is two QVD files, one with 8 weeks, the other with the whole data. Or you do one load unoptimized with these 8 weeks, and store this reduced files into another different QVD files, so the next time you reload you reload from the reduced ones. Even using a variable or something in the file

SET vAllData = 1; // 1 will load from the complete QVD, 0 will load from the reduced QVD you have created with this where.
Table:
LOAD *
FROM File_$(vAllData).qvd (qvd);

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Load only last 8 weeks data

hopkinsc — Thu, 20 Oct 2011 09:34:35 GMT

Thanks Miguel.

Instead i have loaded in my calender first using your where clause

WHERE Date(TransStartDate) >= Date(Today() -56);

as the calender is smallest table, so an unoptimised load isn't a problem.

Then i used where exists clause on the other tables.

It seemed to work locally, i am now just about to try it on the server.

Thanks for your help...