Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bgarland
Contributor III

Reload Advice

So I have never used partial reload or dynamic update, but I now have an application do so and I'd like some advice on the best way to set it up.

My script loads about 30 million records of inventory transactions that cover 24 months, up through midnight last night. I now have access to a database view my DBA created that is identical except that it provides near real-time data SINCE midnight last night. This will give users up to the minute inventory transactions, as well as history, so that they always know what is going on with a particular product.

So I load the large history QVD each morning but how do I set up my script and Publisher task to pick up the "real time" updates throughout the day, but without reloading the 30 million row history each time? Below is what my current script looks like where I'm loading it all, but just once per day.

Transaction_History:

LOAD

  Branch_Plant,

  SKU,

  Fiscal_Period_Date,

  Order_Number,

   %SKULotCodeKey,

  Order_Type,

  Order_Type_Description,

  Document_Type,

  Document_Type_Description,

  Reason_Code,

  Reason_Code_Description,

  Lot_Code,

  Transaction_Date,

  Transaction_Reference,

  Transaction_Explanation,

  Transaction_Quantity,

  Lot_Status_Code,

  Lot_Status_Code_Description

FROM

Detail_Inventory_Transactions_MultiYear.qvd

(qvd);

Current_Transactions:

Concatenate ("Transaction_History")

LOAD

  "Branch_Plant",

  SKU,

  "Fiscal_Period_Date",

  "Order_Number",

  Text(SKU)&Text(Lot_Code) as %SKULotCodeKey,

  "Order_Type",

  "Order_Type_Description",

  "Document_Type",

  "Document_Type_Description",

  "Reason_Code",

  "Reason_Code_Description",

  "Lot_Code",

  "Transaction_Date",

  "Transaction_Reference",

  "Transaction_Explanation",

  "Transaction_Quantity",

  "Lot_Status_Code",

  "Lot_Status_Code_Description"

;

SQL SELECT *

FROM "Fg_DataMart".dbo."Detail_Inventory_Transactions_Changes";

4 Replies
Luminary
Luminary

Re: Reload Advice

Hi Brian,

If your load is optimised its no problem for QlikView.

Best way to do this is to set up an extract qvw layer which handles incremental loading. I have an example here http://community.qlik.com/docs/DOC-7563 detailed videos on how this works is on my blog. QlikCentral.com

Best regards

Richard

bgarland
Contributor III

Re: Reload Advice

While this data is loading optimized, the app goes on to load other data (e.g. production, sales) and perform a number of transformations and calculations. In total, it takes about 7 minutes for the app to do a full reload. However, the users are wanting a 10 minute refresh, at most, which is why I'm trying to see if I can do a partial reload of just that second table or maybe utilize a dynamic update since that second table is essentially "live" data. Obviously, doing a seven minute reload every ten minutes is not the way I want to go. And I don't really understand your incremental approach or how it could be applied to my situation.

Luminary
Luminary

Re: Reload Advice

Hi Brian,

Sorry for the delay in responding.

This incremental load technique is the best way (in my opinion) of handling frequent reloads. It works on the principle of taking small bit size chunks and saving it to a QVD file. Most of the time taken is loading and saving the QVD files although this can be optimised by using monthly (or even daily) qvd files.

Once the data is stored in a QVD file then the dashboard can reload super fast and there's no need for a partial re-load.

Richard

bgarland
Contributor III

Re: Reload Advice

Ok, thanks for the reply.

Community Browser