Loading QVD file

[francisvandergr]

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 ?

[suniljain]

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);

[rbecher]

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

[#4 · Accepted solution]

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);

[rbecher]

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

- Ralf

[#6]

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.

[stevedark]

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

[#8]

...good point about the optimized load.