topic Load multiple files into one qvd in Integration, Extension & APIs

Load multiple files into one qvd

bjagrelli — Sat, 16 Nov 2024 17:30:00 GMT

So I have this script that joins a lot of .qvd files I have in my Drive. But every week I upload new qvd files there, so every time I run this script it has too look for a bunch of files and it takes longer and longer to load it.

What I want to do have a single file that contains the data from all of them, so that I can delete the older ones and keep adding data from new files to this single file.

Is it possible? This is my script right now

LIB CONNECT TO 'GoogleDrive_metadata'; LET folder_id = 'FOLDERID'; tmpFiles: LOAD title ; SELECT title FROM ListFiles WITH PROPERTIES ( query='title contains ".qvd" and trashed = false' ); for iFile = 0 to NoOfRows('tmpFiles') -1 let vFile = peek('title', iFile, 'tmpFiles'); UnificaçãoDados: LOAD *, filename() as FileName FROM [lib://GoogleDrive/$(folder_id)/$(vFile)] (qvd); Next Store UnificaçãoDados into [lib://GoogleDrive/ALLDATA.qvd] (qvd); DROP TABLE tmpFiles;

Re: Load multiple files into one qvd

QFabian — Wed, 10 Feb 2021 18:00:14 GMT

Hi @bjagrelli , please check the incremental loads technics that Qlik offers :

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-files-incremental-load.htm

Re: Load multiple files into one qvd

Denisg — Sat, 13 Mar 2021 13:46:14 GMT

Hi,

Your script is probably taking a lot of time because of the For..Next loop.

A faster solution would be to reload with a wildcard name (...*.QVD) only the new QVDs, with a filter on a [FileName] field.

Here is an example that you can adapt to your context:

// Load AllData.QVD
If isnull(QvdCreateTime('AllData.QVD')) Then
AllData: LOAD * INLINE [FileName2]; // First time: Creates empty AllData.QVD
Else
AllData: LOAD *, FileName as FileName2 FROM AllData.QVD (QVD);
End if

// Accumulate ONLY new QVDs into NewData
NewData: LOAD filename() as FileName,* FROM File*.QVD (QVD) // Add [FileName] field
WHERE NOT EXISTS(FileName2, filename());

// Add NewData into AllData
CONCATENATE (AllData) LOAD * RESIDENT NewData;
DROP TABLE NewData;
DROP FIELD FileName2;

// Save AllData.QVD
STORE AllData INTO AllData.QVD (QVD);