QlikView Documents

[Josh_Good]

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;

[#2]

Thanks , useful post.

[vijetas42]

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

[valerio_fatatis]

Great example, very efficacy.

[IAMDV]

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]

Nice Post

[geetaalhan]

Thanks for sharing.

Regards,

Geeta

[ashfaq_haseeb]

Thanks for sharing

[balkumarchandel]

Thanks for sharing,

Well explained and useful !!

[clr]

Great example, simple and clean.

[sanjyotpatkar]

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 vFileName =  fieldvalue('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;