Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Incremental Load

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

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

Not applicable

Re: Incremental Load

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

Not applicable

Re: Incremental Load

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

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

Re: Incremental Load

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

Not applicable

Re: Incremental Load

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

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

Re: Incremental Load

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

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

Community Browser