Incremental Load

[#1]

I am attempting my first incremental load using a QVD file and am about 99% of the way there.

My QVD file (SalesHistory.qvd) has a field called 'Sales History Index' and is simply a sequential number managed by the DB.

All I need to know is how to retrieve the maximum "Sales History Index'" from the SalesHistory.qvd file and then feed that value back into the SQL query. IE select SalesHistoryRecords from SalesHistoryTable where recnum is greater than the maximum "Sales History Index" value in the SalesHistory.qvd file.

Any help would be greatly appreciated.

Sales_History:

LOAD
 recnum as [Sales History Index],
 stock_namekey as [Stock NK],
 debtor_namekey as [Store NK],
 warehouse as [Warehouse],
 transaction_date as [Sale Date],
 month(transaction_date) as [Sales Month],
 year(transaction_date) as [Sales Year],
 quantity_sold_00 as [QTY Sold],
 week(transaction_date) as Week,
 invoice_time_0 as Hour,
 unit_sale_value_inctax * quantity_sold_00 as [Sale Value],
 unit_sale_value_inctax as [Unit Value];

SQL SELECT recnum, stock_namekey, debtor_namekey, warehouse, transaction_date, quantity_sold_00, invoice_time_0, unit_sale_value_inctax
FROM sales_history
WHERE recnum > ???;


CONCATENATE

LOAD * FROM SalesHistory.qvd (qvd);

STORE Sales_History into SalesHistory.qvd;

[#2]

Hi,

It looks you need the number of records in the QVD file. That is

let cntr=QvdNoOfRecords ('M.qvd')

The SQL becomes .. WHERE recnum > $(cntr) ..

-Alex

[#3 · Accepted solution]

Hi,

In general the script is

MAX_HIST:

LOAD max([Sales History Index]) as max_hist

FROM [SalesHistory.qvd] (qvd);

let o=peek('max_hist', 0, MAX_HIST);

-Alex