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
