topic Incremental Load in QlikView

Incremental Load

MK_QSL — Mon, 02 Dec 2013 13:31:59 GMT

I have sales file for which I need to do incremental load.

While doing incremental Load, need to consider NEW RECORDS as well as update in existing record’s Despatched Column.

Sample file enclosed. Let me know what is wrong in this script… There are two worksheet in excel file...

Re: Incremental Load

Mon, 02 Dec 2013 13:55:59 GMT

I suspect there is date format problem in the excel file.Please find below some points & sample script

-> For Max Date, load only particular field like below.

-> Please make sure that QVD exist or not. If QVD exist do Incremental Load else do Full Load from source

-> Please make sure that date formats should be same in source & variable.

Script:

Let vQVDExist = IF(FILESIZE('C:\Incremental Load\Sales.qvd')>0,-1,0)

IF vQVDExist THEN // Inc Load

Max_DT:

LOAD Max([Invoice Date]) AS MAX_DT,

FROM (qvd);

Let vMaxInvoiceDate = Num(Peek('MAX_DT'));

DROP Table Max_DT;

Incremental:

NoConcatenate

LOAD [Customer ID],

[Customer Name],

[Invoice No],

[Invoice Date],

[Part Number],

QTY,

Despatched

FROM (ooxml, embedded labels, table is Update)

Where Num([Invoice Date]) >= $(vMaxInvoiceDate); //Date interpretaion is need to be check. please make sure that both sides needs to be same format. If the date is text in the excel, please convert into date number.

Concatenate

LOAD [Customer ID],

[Customer Name],

[Invoice No],

[Invoice Date],

[Part Number],

QTY,

Despatched

FROM (qvd)

Where Not Exists ([Invoice No]);

Store Incremental into 'C:\Incremental Load\Sales.qvd'(QVD);

Drop Table Incremental;

ELSE //Full Load

Full:

LOAD [Customer ID],

[Customer Name],

[Invoice No],

[Invoice Date],

[Part Number],

QTY,

Despatched

FROM (ooxml, embedded labels, table is Update);

Store Full into 'C:\Incremental Load\Sales.qvd'(QVD);

Drop Table Full;

ENDIF

Re: Incremental Load

MK_QSL — Mon, 02 Dec 2013 14:43:07 GMT

Thanks for your reply but using above method, I am still unable to update Despatched column.

Re: Incremental Load

Mon, 02 Dec 2013 14:48:11 GMT

I checked your data and You need to Use the Dispatched field in the date check.

Re: Incremental Load

Mon, 02 Dec 2013 15:04:02 GMT

You cann't use [Invoice Date] or Despatched date for Date check in Where clause. You need UpdatedDate field to implement the incremental load.

If you use the Despatched field in Where clause you missed some data.

In your case it seems to difficult to implement the Incremental load because you missed some data.

Re: Incremental Load

MK_QSL — Mon, 02 Dec 2013 15:06:44 GMT

In that case, if any updated Despatched Date is less than Max_Date, it will not be updated.... I know this case is rare but actually we are manually updating Despatched Date and there is a maximum possibility of human error...

Re: Incremental Load

Mon, 02 Dec 2013 15:26:33 GMT

The Despatched filed is not Date field and it is mixed of date & string.

Re: Incremental Load

Mon, 02 Dec 2013 15:32:32 GMT

I believe, you need to create some more logic to check the Despatched field is updated or not ?

How much data you have in the Excel File ?

Re: Incremental Load

MK_QSL — Mon, 02 Dec 2013 15:39:02 GMT

Our system is not supporting my department's process of handling dispatches of customer's consignment, so we are doing manual work on the same.

There are currently 80,000 lines in sales file and it's takes lots of valuable time to reload data.

We have to reload the application at least once in every hour...!!!

Re: Incremental Load

Mon, 02 Dec 2013 16:40:20 GMT

The 80k rows won't much time on the server.

Please create the test task and reload the application on the server

Re: Incremental Load

MK_QSL — Tue, 03 Dec 2013 10:38:19 GMT

I Know but this is not the permanent solution. Business is growing and need to have better way to deal with this.

Re: Incremental Load

stevedark — Mon, 24 Mar 2014 23:47:39 GMT

You may want to see this post on Incremental Load:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

And this one on processing source files to QVD equivalents, so the source is only processed once:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

The post focusses on CSV files, but could easily be adapted to work over QVD files.

Once you have the QVD's doing WHERE EXISTS statements to replace old rows by loading the files from newest to oldest but only each ID once should work. If there is no unique ID you will need to create one by concatenating (or creating a Hash of) a number of fields.

Hope that helps,

Steve

Re: Incremental Load

sunilkumarqv — Tue, 25 Mar 2014 07:22:39 GMT

Hi Manish,

try to change Dispatched Date formate I think both of Invoice Date and Dispatched Date conflicting so possibly

it works in that way I guess .