3 Replies Latest reply: Dec 16, 2015 2:10 PM by Adam Krepistman RSS

    Attempting to load from two distinct QVD directories

    Andy Bross

      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. [C:\DevData\Applications\HistoricalQVDs\*Test.qvd](qvd);

       

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

        • Re: Attempting to load from two distinct QVD directories
          Massimo Grossi

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

            • Re: Attempting to load from two distinct QVD directories
              Andy Bross

              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

            • Re: Attempting to load from two distinct QVD directories
              Adam Krepistman

              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