Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hopkinsc
Valued Contributor II

Load only last 8 weeks data

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

4 Replies

Re: Load only last 8 weeks data

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

hopkinsc
Valued Contributor II

Load only last 8 weeks data

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

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

hopkinsc
Valued Contributor II

Load only last 8 weeks data

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...

Community Browser