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

[#1]

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.

FileName	FileTime	RowCount
File 5.qvd	1.1.2012 05:01:01	55
File 4.qvd	1.1.2012 04:01:01	44
File 3.qvd	1.1.2012 03:01:01	33

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

[#2]

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

[jonathandienst]

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

[#4]

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

[flipside · Accepted solution]

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

[#6]

It's a cleaner code so that helps, but still it takes 44 sec to loop through all the files.

[flipside]

Try moving the SubField line to the bottom step ...

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

    NewTbl:

    LOAD

        '$(file)' as filepath,

        FileTime('$(file)') as newdatefile

        autogenerate 1;

Next;

    ActiveQVDFiles:

    FIRST $(HowManyFiles) Noconcatenate LOAD

        *,

        QvdNoOfRecords(filepath) as fileCount,

        SubField(filepath,'\',-1) as newfilename

        resident NewTbl

        order by newdatefile desc;

DROP Table NewTbl;

It might shave a few seconds off.

[#8]

It did almost nothing.

43 sec now.

Thanks anyway.

My purpose is to get the median of the rowcount of the 9 newest files so I can trigger some alarm if the rowcount is too low. So if there is any other solution that's quicker I would appreciate that.

regards, Kári

[#9]

I found a solution for the time-problem.

You of course didn't know this. My files have a timestamp in the filename so I create a much shorter filelist to loop through.

let year = year(Now());
let month = num(month(Now()));
let day = day(Now());
let yesterday = day(Now())-1;
let RootPath = 'Q:\Filepath\*$(year)$(month)$(day)*.qvd';
let RootPath2 = 'Q:\Filepath\*$(year)$(month)$(yesterday)*.qvd';

so I loop through 2 filelists and it only took 2 sec.

thanks for the contribution.

Kári