Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP

Incremental Load

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

12 Replies
Not applicable

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

MK_QSL
MVP
MVP
Author

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

Not applicable

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

Not applicable

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.

MK_QSL
MVP
MVP
Author

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

Not applicable

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

Not applicable

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 ?

MK_QSL
MVP
MVP
Author

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

Not applicable

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

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