Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Script for Incremental Loading of Multiple QVDs from a List of Table Names

cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Good
Employee
Employee

Script for Incremental Loading of Multiple QVDs from a List of Table Names

Attachments

Below is a script that was developed to quickly pull the fields from a list of tables into QVDs and perform incremental loads on the QVDs going forward.  This particulate script was created to pull data from Saleforce.com however it can easily be adapted for other sources as well.  The script depends on the primary key having the same name between all tables.  Instructions for using the script are included in the comments.

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

Comments
Josh_Good
Employee
Employee

‌Sanjyot,

My guess is the fields in your two tables are the same so Qlik is concatenating them. Therefor on your second pass Echannles does not exist (unless you add noconcatenate). Try dropping the table after you store it. Note this will mean at the end of your load you will have no tables loaded into your app and only QVDs.

-Josh

Qlik

0 Likes
sanjyotpatkar
Creator III
Creator III

yes.. I tried that and it worked fine.. Thanks for your assistance

0 Likes
iluilyas
Contributor III
Contributor III

A very crispy one.

However, its not possible to have a same primary key field name for all tables.

So, i did below modifications:

Instead of specifying only table names, i mentioned table name and primary key column name concatenated with "+" sign, like below:

TablesList:

Load * Inline [

TableName

QS_TABLE_ONE+QS_ID+

];

And while looping, i stripped the info:

For i = 0 to (NoOfRows('TablesList')-1);

  Let varTableName = SubField(Peek('TableName',$(i), 'TablesList'),'+',1);

  Let varPriKey = SubField(Peek('TableName',$(i), 'TablesList'),'+',2);

And at the end, instead of "Id", we simply specify the primary key variable

$(varTableName)_SQL:

Load *

FROM $(vQVDPath)$(varTableName)_e.qvd (qvd)

WHERE NOT EXISTS($(varPriKey));  

Regards

Ilyas

0 Likes
murugesh
Contributor II
Contributor II

Team,

In Incremental load cant support in SELECT statement, Can you help

 

0 Likes
Version history
Last update:
‎2013-11-14 06:22 PM
Updated by: