To create the monthly QVDs I would assume you only want to do this for the current month (as there is no point recreating old months). There are also only a finite number of file types, so you can loop these. So you could end up with something like:
// Set the current period to variables
let vThisYear = Year(today());
let vThisMonth = right('0' & Month(today()), 2);
// Load the types of file we are dealing with
LOAD FileMask INLINE [
// Loop through these file types
for iFileType = 0 to NoOfRows('Temp_FileMasks') - 1
// Grab the next mask to deal with
let vFileMask = peek('Temp_FileMasks', iFileType, 'Temp_FileMasks');
// Load all data for the current month to refresh the monthly file
FROM $(vFileMask)$(vThisMonth)-*-$(vThisYear)-*.qvd (qvd);
// Write this file away and clear the data down
STORE TempMonthsData INTO $(vFileMask)-Monthly-$(vThisYear)-$(vThisMonth).qvd (qvd);
DROP TABLE TempMonthsData;
// Create a master QVD from all monthly files
FROM $(vFileMask)-Monthly-*.qvd (qvd);
STORE MasterQVD INTO $(vFIleMask)-MasterFile.qvd (qvd);
This is untested code, but hopefully it will point you in the right direction for achieving what you are after. The only extra thing you may have to consider is building for the previous month as well as the current (if files come in at the end or after the end of the month).
Thanks for the code Steve. I'll have to take time to learn and understand it all. This will probably work going forward on a monthly basis as you have the code written but right now I have to do this for a thousand files dating back to December of last year. And, of course, not all the file names are consistent...yet. That is why I am hoping to use an internal date field to get the bulk loaded.
Example of names: NonCompliant CSVs folder:
Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-02-2015
Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-04-2015-15-02-20
Golden Host-Daily Report(DR)-Unix-Interactive_04-13-2015R
Golden Host-Daily Report(DR)-Unix-Interactive_04-13thru20-2015
For the first part I actually found some really nice code. Yet, when I tried to move forward with what you provided me, I got the "...file not found..." message. Then I realized that it dealt with the date fields in the filename. What I need to do is replace the texts with the dates within the file names?
How do I go about replacing the text May-2015 to the date May-2015?
To get the dates:
Date(Date#(SubField('$(vBaseName)','-',2),'MMM'),'MMM') as FileMth, Date(Date#(Left(SubField('$(vBaseName)','-',3),4),'YYYY'),'YYYY') as FileYr;
Step 1) ... good to go
TRACE ==== Auto load source .csv files into .qvd files with same name in different folder ====;
LET vSourcePath = 'E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\';
FOR EachvFileinFileList ('$(vSourcePath)*.csv')
LETvBaseName = Mid(SubField('$(vFile)','\',-1), 1, Index(SubField('$(vFile)','\',-1),'.',-1)-1); IFIsNull(FileTime('$(vSourcePath)\QVDs\$(vBaseName).qvd')) then
What I would suggest is that you take a read of the blog post that I put a link to in my initial comment. This should make things clear.
You can append historical data from one QVD into a new QVD that has both old and new rows. The way that you do this is to load both old and new data into a QVW file and then issue a STORE statement over the top of the existing QVD (or to a new one, if required).
It is a common practice to have separate 'QVD Generator' apps, that LOAD and STORE data and then DROP all tables - so that the app contains no data. The QVW files with the charts then load purely from QVD files.