Attempting to load from two distinct QVD directories

[#1]

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. (qvd);

Can anyone suggest how to update this script to also pull in 'ExtractWeek' from the location specified?

[maxgro]

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_?

[#3]

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

[#4]

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