3 Replies Latest reply: Jul 14, 2015 11:23 AM by Karl Bayeshev RSS

    Incremental Load of New Files Only

      Hi,


      To summarize, what is the way to perform an Incremental Load on a folder full of csv files without Loading the data against the 'Where" condition?

       

      I have a folder with daily files going back 10 years+. To save time, I ran an initial load and Stored everything into a QVD file.

       

      Moving forward, I would like to add data from only New files, but on an ad-hoc basis.

       

      When running an Incremental Load, I do not want to open ALL files to match the "Where" statement. I've set an artificial variable limit (Year portion of the Last_Modified_Date 2015) with a wildcard into the file name, so the Load would only look into the current year. This will be problematic at year-end.

       

      The problem I'm trying to overcome is that, say after 200 days, that is 200 files to load for a match to my Where statement. This takes quite a bit of time. Is there a way to perform an Incremental Load on a folder full of CSV files without Loading the data against the 'Where" condition?    

       

      File Names have have a date stamp in the file name.

       

      I'm a new QlikView user! A code example would help.


      Many thanks!

        • Re: Incremental Load of New Files Only
          Stefan Wühl

          You may consider moving the files to a different folder after loading and storing to your QVD.

           

          Or create a loop of the files and only load the newest files

           

          FOR Each File in filelist (Root&' \*.' &Ext)

           

          Let vCheck = ... // Parse the date from file name and compare to latest Date from QVD

           

          IF vCheck THEN

           

          LOAD Field1,

                    Field2,

                    ...

          FROM $(File);

           

          ENDIF

           

          NEXT File

          • Re: Incremental Load of New Files Only
            Gerardo Alegria Benitez

            You can use a variable with the actual date for loan only the file of this date:

             

            Let vActualDate=today();

             

            Table:

            LOAD

                       *

            FROM

            [..\DATA\FILENAME_$(vActualDate).csv];

             

            Store Table into ..\QVD\ActualQVD.qvd;

             

            and after that you can do a incremental load

             

            Master:

            LOAD

                      *

            FROM

            [..\QVD\HistoricalQVD.qvd];

             

            concatenate(Master)

            LOAD

                      *

            FROM

            [..\QVD\ActualQVD.qvd];

             

            Let me know if this help you.

             

            Ger.

            • Re: Incremental Load of New Files Only

              Thank you! I pursued swuehl second option. Script execution is significantly faster, but I must admit the script takes somewhat longer than I anticipated to pull the file names from my network drive.

               

              My code for reference:

               

              //Loading Data from QVD

              [DATA_TABLE]:

              Load *

              FROM [c:/blah.qvd] (qvd);

               

               

              //Find Last Modified Date

              Last_Updated_Date:

              LOAD Max(Modified_Date) as MaxDate

              resident [DATA_TABLE];

               

               

              //Store Last Modified Date to a Variable

              LET Last_Updated_Date = peek('MaxDate',0,'Last_Updated_Date');

               

               

              LET vStore='No';

              FOR Each vFile in FileList('C:\ABC\TOR*.csv')

               

               

                LET vfilenamedate = Date#(mid('$(vFile)',23,8), 'YYYYMMDD'); 

               

                IF (vfilenamedate > Last_Updated_Date) then

               

                LET vStore='Yes';

               

               

                Concatenate

                LOAD *

                FROM

                [$(vFile)];

                ENDIF;

              NEXT vFile;

               

              // Replace Old QVD file

              IF  (vStore = 'Yes' ) then

                Store [DATA_TABLE] into c:/blah.qvd (qvd);

              ENDIF;