Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

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

Employee
Employee

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

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;

Attachments
Comments
Not applicable

Thanks , useful post.

vijetasharma
Contributor III

can this will work when my old data is also updating?

valerio_fatatis
New Contributor III

Great example, very efficacy.

Luminary
Luminary


Thanks Josh. I've made similar post(s) on my blog. Here is the link (Copy & Paste the URL):

http://qlikshare.com/tag/qvd-generator

Thanks,

DV

perumal_41
Valued Contributor II

Nice Post

geetaalhan
Contributor

Thanks for sharing.

Regards,

Geeta

Thanks for sharing

balkumarchandel
Valued Contributor II

Thanks for sharing,

Well explained and useful !!

Employee
Employee

Great example, simple and clean.

sanjyotpatkar
Contributor II

hi Josh

I am using a similar script for generating multiple qvd during incremental load. Script throws an error while creating the second qvd stating unable to Store Echannels into Echannels qvd.

If I use a no concatenate before the load statement it generates 2 qvds but generates synthetic table between the 2 tables and takes longer to load. I am not sure what is going wrong

FILES:
load * Inline [
FileName
MEHIE
Echannels
]
;

for i = 1 to FieldValueCount('FileName');

let vFileNamefieldvalue('FileName',$(i));

let lastexectime  =  num(date(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT'));
let BeginningThisExectime  =  num(date(now(),'MM/DD/YYYY hh:mm:ss TT'));

$(vFileName):

load *

FROM
[$(DATA_FilePath)$(vFileName).xlsx]
(
ooxml, embedded labels)
where ([Modified Date] >= '$(lastexectime)' and [Modified Date] < '$(BeginningThisExectime)' );

STORE $(vFileName) into $(vFileName).qvd(qvd);


next i;

Version history
Revision #:
1 of 1
Last update:
‎11-14-2013 06:22 PM
Updated by:
Employee