Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all -
As part of a QVD Loader, QVDs are created in two different directories: a "QVDs" directory and a "HistoricalQVDs" directory. The only main difference between all QVDs are the HistoricalQVDs include a timestamped date (called ExtractWeek).
For my front end application the attached script is working correctly and pulling in all the fields I want from the QVDs directory. I also need to pull in 'ExtractWeek' from the "HistoricalQVDs" directory i.e.
Can anyone suggest how to update this script to also pull in 'ExtractWeek' from the location specified?
It depends. Do you want to append the HistoricalQVDs to Qvd or you want to join the 2 different set of files?
In case of join, which are the join fields? _ROWNUM_?
I believe I want this joined. I have included my Loader script in below as reference. So my QVD directory is always pulling in the latest data from my source and the historical directory is saving off a timestamped copy (for right now as i test, its daily, but it will modify to weekly or monthly) -- and the only difference between the QVDs is the historical qvd contains an extra field: ExtractWeek. I just want to be able to report against that as well.
I am almost there, so any further suggestions would be appreciated.
ODBC CONNECT TO removed this string intentionally;
tables:
SQLtables;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = peek('TABLE_NAME', i, 'tables');
LET qvdName = left((peek('TABLE_NAME', i, 'tables')),index((peek('TABLE_NAME', i, 'tables')),'_')-1);
[$(qvdName)]:
LOAD *,
left(('$(qvdName)'),4) as ProjectCode,
'$(qvdName)' as Sheet;
SQL SELECT * from "$(sheetName)";
store [$(qvdName)] into C:\DevData\Applications\QVDs\$(qvdName).qvd (qvd);
drop table [$(qvdName)];
NEXT
tables:
SQLtables;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = peek('TABLE_NAME', i, 'tables');
LET week = date(today(),'YYYYMMDD');
LET qvdName = left((peek('TABLE_NAME', i, 'tables')),index((peek('TABLE_NAME', i, 'tables')),'_')-1);
[$(qvdName)]:
LOAD *,
left(('$(qvdName)'),4) as ProjectCode,
'$(week)' as ExtractWeek,
'$(qvdName)' as Sheet;
SQL SELECT * from "$(sheetName)";
store [$(qvdName)] into C:\DevData\Applications\HistoricalQVDs\$(qvdName)_$(week).qvd (qvd);
drop table [$(qvdName)];
NEXT
Are there currently any applications using the ExtractWee qvd or has it been crated and you are trying to figure out how to use/access it?
The firt thing you will need to decide is like maxgo said, how do you want to include the data - join or concatenate
You will then need to read in the file from the directory it is located in and then join or concetane the table