topic Increamental Load in QlikView

Increamental Load

suniljain — Tue, 13 Jul 2010 18:43:57 GMT

Dear Friend ,

I need help Regarding Increamental Load.

Example : On 1st June Data in QVD

Date Sales Order Qty

1.1.2010 S11001 1000

On 5th June Updated Existing Sales Order in ERP.

Date Sales Order Qty

5.1.2010 S11001 1500

In Final QVD Sales Order 's Quantity Shoulde be 1500.

Regards

Sunil Jain.

Increamental Load

Tue, 13 Jul 2010 19:13:54 GMT

Maybe this can help you for some idea!!!!

The Script below shows you a simple incremental load. This will carry out the basic function of

inserting/adding new data. For more complex incremental loads such as UPDATE and DELETE. Please refer

to the reference manual.

//Find the maximum id or latest date etc to pass to the variable and subsequently the

where clause of the data load

MaxKeyLoad:

LOAD Max(PrimaryKey) as MaxID

FROM MyData.qvd (qvd);

//Peek into the MaxHeadLoad to get the latest value, in this case the MAX load above will

//only bring back 1v ow so the 0 in the variable tells it to bring the first row.

Let MaxID = peek('MaxID',0,MaxKeyLoad);

//Connect to your data source

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;);

//Run the data load to pull data based on the variable to find the latest / newest row

MyData:

LOAD PrimaryKey,

Column1,

Column2,

Column3,

Column4;

SQL SELECT *

FROM DATABASE.MYDATA

Where PrimaryKey > $(MaxID);

//Append the new updated data to the existing historical qvd file

CONCATENATE

LOAD * FROM MyData.qvd (qvd);

//Store the new larger qvd file

Store MyData into MyData.qvd;

Increamental Load

Tue, 13 Jul 2010 19:16:27 GMT

Hi sunil,

If i understood your question below is the answer.

1st create a variable in edit script.

Let VLastReloadTime = ReloadTime();

Table1:

Load Date,

"Sales Order",

Qty

From ...............;

Store Table1 into ;

After creating QVD comment the above code.

Next step will be taking data from DB directly when the Date field which is in table is greater than the last executed date which we have stored in variable (i.e VLastReloadTime) .

Table1:

Load Date,

"Sales Order",

Qty

From ............... where Date > $(VLastReloadTime);

Concatenate

Load Date,

"Sales Order",

Qty

From where not exist(Sales Order,Sales Order);

again store the Table1 data in table1.qvd (i.e replace the old table1.qvd)

Hope this will help you.

-Peterson