8 Replies Latest reply: Dec 5, 2012 7:07 AM by Kári Georgsson RSS

    How to count rows in qvd files, but only of the X newest?

      For example.

       

      I have 5 qvd files in a folder.

       

      File 1.qvd -> have 11 rows - filetime = 1.1.2012 01:01:01

      File 2.qvd -> have 22 rows - filetime = 1.1.2012 02:01:01

      File 3.qvd -> have 33 rows - filetime = 1.1.2012 03:01:01

      File 4.qvd -> have 44 rows - filetime = 1.1.2012 04:01:01

      File 5.qvd -> have 55 rows - filetime = 1.1.2012 05:01:01

       

       

      Say I only want to count rows of the 3 newest files in that folder, I would like to get this result.

       

       

      FileNameFileTimeRowCount
      File 5.qvd1.1.2012 05:01:0155
      File 4.qvd1.1.2012 04:01:0144
      File 3.qvd1.1.2012 03:01:0133

       

       

      I guess I have to create sort of loop logic but I'm not sure How I can skip to read all the other files in the folder.

      The folder could have 40.000 qvd files but I only want  to check the row count of the newest ones.

       

      So, how would I do this ?

       

      regards, Kári

        • Re: How to count rows in qvd files, but only of the X newest?

          It could also be a solution if I could somehow have a where condition on the filetime of the files.

           

          Like, filetime() > '1.1.2012 02:01:01'

           

          If this is a better approach then this could help.

           

          regards, Kári

            • Re: How to count rows in qvd files, but only of the X newest?
              Jonathan Dienst

              Kári

               

              Here's one way to do this

               

              Set vNewestCount = 3;
              Set vLocation = 'C:\Test';
              
               For Each zFile in FileList('$(vLocation)\*.qvd')
                          T_FileTimes:
                        LOAD '$(zFile)' As FileName,
                                  FileTime('$(zFile)') As FileTime
                        AutoGenerate 1;
                Next
              
              T_Select:
              NoConcatenate
              First $(vNewestCount) LOAD * Resident T_FileTimes
              Order By FileTime desc;
                DROP Table T_FileTimes;
              
              For zi = 1 To vNewestCount
                          Let zFileName = Peek('FileName', zi, 'T_Select');
                        Let zFileTime = Peek('FileTime', zi, 'T_Select');
                        Let zRows = QvdNoOfRecords('$(zFileName)');
              
                        Results:
                        LOAD '$(zFileName)' As FileName,
                                  Date($(zFileTime), 'D.M.YYYY hh:mm:ss') As FileTime,
                                  $(zRows) As RowCount
                        AutoGenerate 1;
                Next
              
              DROP Table T_Select;
              

               

               

              Put the correct location in the line setting vLocation and the number of 'most recent' QVDs in vNewestCount. I have not tested this script, but I think it is correct.

               

              Hope that helps

              Jonathan

                • Re: How to count rows in qvd files, but only of the X newest?

                  I already found something similar and it looped through the files faster.

                   

                  Here is the code:

                  Sub DoDir (Root) 
                            For Each File In FileList (Root & '\*.qvd') 
                                      Files: 
                                      Load '$(File)' as Name, 
                                       FileTime('$(File)') as FileTime 
                                      Autogenerate 1; 
                            Next File 
                  
                  
                  End Sub 
                  
                  
                  Call DoDir ('Q:\FilePath);
                  
                  
                  let HowManyFiles = 9;
                  
                  
                  MostRecentFileName:
                  FIRST $(HowManyFiles) LOAD Name AS MostRecentFileName, RowNo() as RowNumber
                  RESIDENT Files
                  ORDER BY FileTime DESC; 
                  
                  
                  let a = 1;
                  LET vFileToLoad = FieldValue('MostRecentFileName', $(a)); 
                  
                  
                  
                  
                  
                  
                            do while a <= $(HowManyFiles)
                  
                            LET vFileToLoad = FieldValue('MostRecentFileName', $(a)); 
                  
                                       FilesInFolder:
                                       LOAD 
                                       FileSize() AS FileSize,
                                       FileName() As FileName,
                                       FileTime() AS FileTime,
                                       count(1) as RowCounter
                                       from $(vFileToLoad) (qvd);
                  
                             let a = a+1;      
                  
                            loop;
                  
                  

                   

                  Is it possible to skip all the 25.000 files I have in the folder because it takes about 1 minute to run.

                   

                  regards, Kári

                    • Re: How to count rows in qvd files, but only of the X newest?
                      Dave Riley

                      Hi Kári,

                       

                       

                      Is this any faster?  It only uses one loop so should be.

                       

                      let RootPath = 'C:\{folder path}\*';

                      let HowManyFiles = 9;

                       

                      For each file in FileList('$(RootPath)')

                       

                          NewTbl:

                          LOAD

                              '$(file)' as filepath,

                              subfield('$(file)','\',-1) as newfilename,

                              FileTime('$(file)') as newdatefile

                              autogenerate 1;

                       

                      Next;

                       

                          ActiveQVDFiles:

                          FIRST $(HowManyFiles) Noconcatenate LOAD

                              *,

                              QvdNoOfRecords(filepath) as fileCount

                              resident NewTbl

                              order by newdatefile desc;

                       

                      DROP Table NewTbl;

                       

                      flipside