Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
flipside
Not applicable

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

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

8 Replies
Not applicable

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

jontydkpi
Not applicable

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

flipside
Not applicable

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

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

Not applicable

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

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

flipside
Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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