// Commets with ************ indicate locations where the script needs to be customized for your use
//************Add Connection String below *************************
//**********************************************************************
Set vQVDPath = QVDs/; //**************** Enter Location to save QVDs in the path should end it back slash. For example: C:\MyQVDs\
// For realtive paths are also possible. For example QVDs\ will put the QVDs in a subfolder from where the QVW is saved.
//Set the variables so the last time of script execution is known
SET vLastExecTime = 0; // resetting vLastExecTime
LET vLastExecTime = timestamp(if(isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD')), 0,ConvertToLocalTime(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');
LET vExecTime = timestamp(UTC(), 'YYYY-MM-DD hh:mm:ss');
LET vTodaysDate = today();
//For the 1st reload, this section will be skipped.
LET FirstReload = isnull(QvdCreateTime('$(vQVDPath)ReloadHistory.QVD'));
if Not $(FirstReload) then
// Read Reload History Data
ReloadHistory:
Load
No,
[Last Reload Ended],
[Reload Started]
FROM $(vQVDPath)ReloadHistory.qvd (qvd);
end if
ReloadHistory:
LOAD
RowNo() as No,
'$(vLastExecTime)' as [Last Reload Ended],
'$(vExecTime)' as [Reload Started]
Autogenerate(1);
//ReloadHistory.qvd will keep track of when the last reload occurred for incremental loading purposes. If you need to reload all the data then delete ReloadHistory.qvd from your file structure.
STORE * FROM ReloadHistory INTO $(vQVDPath)ReloadHistory.qvd;
//*****************Add a list of tables to be loaded from your data source below. Keep the first row "TableName". This is the name of the field.
TablesList:
Load * Inline [
TableName
Account
Opportunity
];
For i = 0 to (NoOfRows('TablesList')-1);
Let varTableName = Peek('TableName',$(i), 'TablesList');
// Load Tables
$(varTableName)_SQL:
SQL Select *
FROM $(varTableName)
WHERE LastModifiedDate >=$(vLastExecTime) and LastModifiedDate < $(vExecTime); //***************************Change the "LastModifiedDate" to the name of the field for the last modified data time stamp in your tables.
// For the 1st reload, this section will be skipped.
if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)$(varTableName)_SQL.qvd')) then
//CONCATENATE ($(varTableName))
$(varTableName)_SQL:
Load *
FROM $(vQVDPath)$(varTableName)_SQL.qvd (qvd)
WHERE NOT EXISTS(Id); // *************************************** Change "Id" to the primary key for your tables (note all your tables must use the same name for the primary key for this script to work.
end if
// If table exists then proceed to the next step
if NoOfRows('$(varTableName)_SQL') > 0 then
STORE $(varTableName)_SQL INTO $(vQVDPath)$(varTableName)_SQL.qvd;
DROP TABLE $(varTableName)_SQL;
End if
Next;
Drop Table TablesList;
