Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

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

Josh_Good
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.

0 Likes
vijetas42
Specialist
Specialist

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

0 Likes
valerio_fatatis
Partner
Partner

Great example, very efficacy.

0 Likes
IAMDV
Luminary Alumni
Luminary Alumni


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

0 Likes
perumal_41
Partner
Partner

Nice Post

0 Likes
geetaalhan
Creator
Creator

Thanks for sharing.

Regards,

Geeta

0 Likes
ashfaq_haseeb

Thanks for sharing

0 Likes
balkumarchandel
Specialist II
Specialist II

Thanks for sharing,

Well explained and useful !!

0 Likes
clr
Employee
Employee

Great example, simple and clean.

0 Likes
sanjyotpatkar
Creator III
Creator III

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;

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