QVD Files to be update by Incremental loads, delta changes to data

[andre_avenant]

Hello All

I am trying to get the delta changes to be written to the qvd file. The QVD file i then reload in another model. So I think there are 2 options: one is just to update the delta changes, changes to the data base or then do a complete reload of the QVD.

Let me know if I am on the correct path.

This is my script below, it does not seem to work:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
LIB CONNECT TO '123456 (administrator)';

LET lastReloadTime = ReloadTime();

Combined:
LOAD "name",
season,
supplier,
"sales_week"
FROM [lib://QVDDATA/1812.QVD]
(qvd);

Concatenate(Combined)

Load "name",
season,
supplier,
"sales_week"
FROM ExportersDB.dbo."v_sales_data_combined"
Where (Calendardate >$(lastReloadTime));

STORE Combined INTO [lib://QVDDATA/1812.QVD] (qvd);

[andoryuu]

If your data can be concatenated that way (only ever requires inserts) then this is the best way. However, you should not rely on the ReloadTime(). That will have the time when it FINISHED reloading. This leaves you with the potential for missing records. Instead assign now() to your variable to get the records since you last STARTED to load.

[Taoufiq_Zarra]

Hi,

If I understand you request you want to do an "Insert and Update".

with this code :

you're going to load the QVD data on which you're going to add the modified data after your "lastReloadTime" data.

LIB CONNECT TO '123456 (administrator)';

LET lastReloadTime = ReloadTime();

Combined:
LOAD "name",
season,
supplier,
"sales_week"
FROM [lib://QVDDATA/1812.QVD]
(qvd);

Concatenate(Combined)

Load "name",
season,
supplier,
"sales_week"
FROM ExportersDB.dbo."v_sales_data_combined"
Where (Calendardate >$(lastReloadTime));

STORE Combined INTO [lib://QVDDATA/1812.QVD] (qvd);

except you didn't make any update.

For what you're going to do, you need to identify the key of your table.

I propose these modifications

note that the Calendardate date corresponds to the modification date

New code:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

LIB CONNECT TO '123456 (administrator)';

LET lastReloadTime = ReloadTime();

Combined:

Load
PrimaryKey,
"name",
season,
supplier,
"sales_week"
FROM ExportersDB.dbo."v_sales_data_combined"
Where (Calendardate >=$(lastReloadTime));

concatenate

LOAD 
PrimaryKey,
"name",
season,
supplier,
"sales_week"
FROM [lib://QVDDATA/1812.QVD] (qvd)
WHERE NOT Exists(PrimaryKey);

STORE Combined INTO [lib://QVDDATA/1812.QVD] (qvd);

[andre_avenant]

Hello, thanks for the info

I seem to get a error when trying to reload:
[cid:image001.png@01D5C48C.2BF304F0]

[Taoufiq_Zarra]

No picture attached !

[andre_avenant]

Lets try again:
[cid:image001.png@01D5C48C.C3102B60]

[Taoufiq_Zarra]

No

[andre_avenant]

Lets try again: 

[Taoufiq_Zarra]

change : Where (Calendardate >=$(lastReloadTime));

to

Where (Calendardate >='$(lastReloadTime)');

and try ?

[andre_avenant]

still , nope

and also get this is tne output: 

15:18:03 A

Unexpected token: 'Calendardate', expected one of: 'codepage', 'Comment', 'biff', 'dif', 'fix', 'html', 'json', ...: CombinedData: Load PrimaryKey, "name", season, supplier, "sales_week" FROM ExportersDB.dbo."v_sales_data_combined" Where (>>>>>>Calendardate<<<<<< >='1/6/2020 3:18:03 PM')