- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Loading QVD file
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 ?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Right, but you should consider what is in your folder! It's sometimes better to loop over a file list... 😉
- Ralf
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
...good point about the optimized load.