Incremental load

ecabanas — Fri, 22 May 2015 09:26:47 GMT

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

Re: Incremental load

vardhancse — Fri, 22 May 2015 09:29:53 GMT

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

Re: Incremental load

ecabanas — Fri, 22 May 2015 09:40:28 GMT

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

Re: Incremental load

vardhancse — Fri, 22 May 2015 09:53:59 GMT

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

Re: Incremental load

vardhancse — Fri, 22 May 2015 09:55:33 GMT

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

topic Re: Incremental load in QlikView

Incremental load

Re: Incremental load

Re: Incremental load

Re: Incremental load

Re: Incremental load