topic Loading QVD file in QlikView

Loading QVD file

francisvandergr — Thu, 24 Mar 2011 10:06:39 GMT

I have made some QVD files. Now i want to reload those file in an new QVW. I have the following QVD files:

23-03-2011_File1

24-03-2011_File2

In those files i have 2 fields eg: Salesperson and Amount. In my load script i want to add 23-03 and 24-03 when loading the QVD files. Is this possible ?

Loading QVD file

suniljain — Thu, 24 Mar 2011 10:09:44 GMT

you can perform delta load of 23-3 and 24-3 to existing data. following is just a template.

load

from

23-3

concatenate

load

from historical where not exists(primarykey);

Loading QVD file

rbecher — Thu, 24 Mar 2011 10:19:15 GMT

Hi Francis,

try something like this:

Result:
LOAD date#('23-03-2011','DD-MM-YYYY') as Date, Salesperson, Amount
FROM 23-03-2011_File1.qvd (qvd);

LOAD date#('24-03-2011','DD-MM-YYYY') as Date, Salesperson, Amount
FROM 24-03-2011_File1.qvd (qvd);

- Ralf

Loading QVD file

Thu, 24 Mar 2011 10:26:02 GMT

Just building on the top of Ralf here...

Why not:


<pre>Result:
LOAD date#(LEFT(FILENAME(),10),'DD-MM-YYYY') as Date, Salesperson, Amount
FROM *_File1.qvd (qvd);

Loading QVD file

rbecher — Thu, 24 Mar 2011 10:29:31 GMT

Right, but you should consider what is in your folder! It's sometimes better to loop over a file list... 😉

- Ralf

Loading QVD file

Thu, 24 Mar 2011 10:34:31 GMT

yes, yes, of course...i only wanted to make the point, that you don't need to add each file separately, because that requires you to change the script every time you get a new file.

Loading QVD file

stevedark — Fri, 25 Mar 2011 07:52:44 GMT

Hi there,

A couple of observations. First up, another alternative to listing all source files or using a star is looping through each file in code. This allows you to add logic to decide whether you want to take the file or not. For example here I am loading from a folder of Excel files - but want to ignore the temp files created when a file is opened:

For each vFileName in filelist(vSourceFolder & vFileMask)
if index(vFileName, '~$') = 0 then // ~$ denotes an open file temp file
LOAD * FROM [$(vFileName)]
(biff, no labels, table is RT$);
end if
next

The other observation is that parsing the filename of the QVD and using that as a date will work fine, but it will cause a non optimized QVD load. It will be much more efficient to write the date into the QVD file at time of creation (it's obviously known as it's in the file name). For more information on Optimized vs. Non Optimized search the community - but just to let you know it can be up to 100x quicker!

Hope that helps,
Regards,
Steve

Loading QVD file

Fri, 25 Mar 2011 08:31:39 GMT

...good point about the optimized load.