0 Replies Latest reply: Aug 11, 2015 12:02 PM by Mindy Kay RSS

    Incremental Loop Daily CSVs into Monthly QVDs then Master QVD

      All,

       

      I am NEW to this program coding and I'm having great frustration with this incremental loop.  I've read many great blogs and how-to's but still can't figure this out or find something more closely to what I want to accomplish.  And, everything I've tried to-date isn't working.  Is there anyone out there that can help?

       

      Thanks,

      Mindy

       

       

      Scenario:

      Daily csv files are loaded into folders.

      1)  I want to load only new csv files into qvd files (this part is done).

                ASRCompliant31-Jul-2015_00_00.csv    > ASRCompliant31-Jul-2015_00_00.qvd

                ASRCompliant11-Aug-2015_00_00.csv  > ASRCompliant11-Aug-2015_00_00.qvd

       

      2)  Load only new rows of data into a corresponding Monthly QVD based on the date field, SESSION_START_TIME (aka EventMth).

                ASRCompliant31-Jul-2015_00_00.qvd    > Compliant_Events_Jul-2015.qvd         

                ASRCompliant01-Aug-2015_00_00.qvd  > Compliant_Events_Aug-2015.qvd...and  possibly...Compliant_Events_Jul-2015.qvd

               

      3)  Load the Monthly QVDs into a Master QVD.

                Compliant_Events_Jul-2015.qvd     > Combined_Events.qvd

                Compliant_Events_Aug-2015.qvd   > Combined_Events.qvd

       

       

      STEP 1:

      // ===== Auto load source Compliant .csv files into .qvd files with same name in different folder =====//

      SET vSourcePath = 0;
      LET vSourcePath = 'R:\Events\TEST\Compliant\';

       

      FOR Each vCSVFile in FileList('$(vSourcePath)*.csv')

              LET vBaseName = Mid(SubField('$(vCSVFile)','\',-1), 1, Index(SubField('$(vCSVFile)','\',-1),'.',-1)-1);
              IF IsNull(FileTime('$(vSourcePath)\QVDs\$(vBaseName).qvd')) then

                      tmpTable:
                      LOAD

                              'Compliant' as Compliancy,
                              DATA_SRC_ID as [Event ID],

                              DATA_SOURCE,
                              MonthName(Date#(SESSION_START_TIME, 'DD-MMM-YYYY hh:mm:ss')) as EventMth,
                              FileName() as Source_File,
                              FileBaseName() as File_Base_Name,
                              FilePath() as File_Path

                      From

                             [$(vSourcePath)\$(vBaseName).csv]
                             (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq);
                      STORE tmpTable into [$(vSourcePath)\QVDs\$(vBaseName).qvd] (qvd);
                      DROP Table tmpTable;
            END If
      NEXT vCSVFile

       

      STEP 2: ?

      STEP 3: ?