6 Replies Latest reply: Jul 24, 2013 10:46 AM by Andrei Popescu RSS

    How can I load from QVD files using an incremental load based on File Date

      Hi guys,

       

      I have a quick question.

       

      I have this QVD files that look like this SalesEntry_20130401.qvd

                                                              SalesEntry  20130402.qvd

                                                              SalesEntry  20130403.qvd

       

      I will like to concatenate load the files in a master SalesEntry.qvd that will run each day and only select the files newer than the last reload time.

       

      Many thanks for anyone that can help.

        • Re: How can I load from QVD files using an incremental load based on File Date
          Vishwaranjan Kumar

          try accoding to this example

           

          MASTER:

          LOAD pid,

               pname,

               price

          FROM

          [D:\SAP\QLIKVIEW-QVW-QVD FILE_NEW_2013\INCREMENTAL\MASTER.QVD]

          (qvd);

           

          TEMP:

          LOAD MAX(pid) AS MAXPID

              Resident MASTER;

           

          DROP Table MASTER;   

           

          LET Vmax = Peek('MAXPID',-1,'TEMP');

           

          TRANSACTION:

          LOAD pid,

               pname,

               price

          FROM

          [D:\SAP\QLIKVIEW-QVW-QVD FILE_NEW_2013\INCREMENTAL\TRANSACTION.xlsx]

          (ooxml, embedded labels, table is TRANSACTION) Where pid >$(Vmax);

           

          Join

          LOAD pid,

               pname,

               price

          FROM

          [D:\SAP\QLIKVIEW-QVW-QVD FILE_NEW_2013\INCREMENTAL\MASTER.QVD]

          (qvd) Where NOT Exists(pid);

          • Re: How can I load from QVD files using an incremental load based on File Date
            Gysbert Wassenaar

            It's easiest if you can move the daily qvd files after you've loaded them. See here for example. Otherwise you'll have to do something based on parts of the filename. See here. Or the links in this discussion: Dynamic Update and Incremental Loading

              • Re: How can I load from QVD files using an incremental load based on File Date

                Thanks Gysbert,

                 

                I looked at your examples and non of them fits.

                I was thinking more like 

                 

                Set vConcatenate = ;

                sub ScanFolder(Root)

                          for each FileExtension in 'qvd'

                                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                                              FileList:

                                              $(vConcatenate)

                                              LOAD '$(FoundFile)' as SourceFile,QvdCreateTime('$(FoundFile)') as CreateTime

                                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                                                   If QvdCreateTime('$(FoundFile)') < '$(vThisExecTime)'

                                                   MasterSalesEntry:

                                                   LOAD *,

                                                   FROM   $(FoundFile) (qvd);

                                                   Endif

                                              Set vConcatenate = Concatenate;

                                              TRACE $(vConcatenate);

                                    next FoundFile

                          next FileExtension

                end sub

                 

                Call ScanFolder('D:\Work\QlikView\TLC\Qlikview Master - 2009 R2 - TLC\Data\QVD') ;

                 

                STORE MasterSalesEntry INTO $(vDataFolder)MasterSalesEntry.qvd (qvd);

                 

                 

                But

                                                   If QvdCreateTime('$(FoundFile)') < '$(vThisExecTime)'

                                                   MasterSalesEntry:

                                                   LOAD *,

                                                   FROM   $(FoundFile) (qvd);

                                                   Endif

                 

                statement does not seems to work.

                 

                Thanks

              • Re: How can I load from QVD files using an incremental load based on File Date
                Piet Hein van der Stigchel

                You can try something like the following:

                 

                DatesOfFiles:

                LOAD MaxString(Mid(FileBaseName(),12, 8)) AS PossibleDates

                FROM

                *.qvd

                (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

                 

                 

                LET vLastDate = Peek('PossibleDates', -1);

                LET file='SalesEntry_'&vLastDate&'.qvd';

                 

                 

                DROP TABLE DatesOfFiles;

                 

                Load *

                FROM

                $(file)

                (txt, codepage is 1252, embedded labels, delimiter is ';', msq);