Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

Incremental load

Hi all,

I would like to do an incremental load from a Data base that contains Date, source and Visitors fields. This table updates twice a day (00:30 and 18:00) and I would like to do an incremental load.

The problem is I would like to update (or erase) the today data and insert in the final QVD.

Does anybody has an example script to do this?

Many many thank's

Eduard

4 Replies
vardhancse
Specialist III
Specialist III

//  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;

ecabanas
Creator II
Creator II
Author

Hi Sasi,

First of all, many thank's for your help, but the problem is that I did not use an ID, is a non unique date field....

I need to reload only the today data twice per day

Eduard

vardhancse
Specialist III
Specialist III

We should have one primary key, one date field for doing incremental load

vardhancse
Specialist III
Specialist III

We can schedule the load in the QMC task, but the logic should have one primary key and one date field to identify that insert/modify