Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

QVD Comparison

Hi ,I want to  show the count of rows in the qvd file generated yesterday and count of rows in qvd file generated today.So that it can be used as a data quality application.Can some one help me with the logic

Expected out put :

Name of QVDRecord Count YesterdayRecord Count Today
Sales.qvd50005050
invoice.qvd30003500
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

IF  (isNull(qvdCreateTime('..\QVD\QVD_Monitor.qvd'))) THEN

For each vFile in FileList('M:\Qlikview_IMO_Analytics\Dev - KK\QVD\*.qvd*')

     let vFileSize    = Alt(FileSize('$(vFile)'), 0);

     let vRecordCount = Alt(QvdNoOfRecords('$(vFile)'), 0);

     let vCreatedon   = Alt(FileTime('$(vFile)'), 0);

     let vDaysBehind =  num(Today()) - num(floor(vCreatedon));

     

     QVD_Monitor:

    

       LOAD

          '$(vFile)'      as FileName,

          $(vCreatedon)   as GeneratedOn,

          $(vRecordCount) as RecordCount,

          $(vFileSize)    as FileSize,

          $(vDaysBehind)  as NumDaysBehind,

           NOW()          AS ReloadedOn

          //RangeSum(Peek('FileCount'), 1) as Filenum    

     AUTOGENERATE(1)

     ;

Next 

STORE QVD_Monitor into [..\QVD\QVD_Monitor.qvd];

ELSE

QVD_Monitor:

LOAD FileName,

     GeneratedOn,

     RecordCount,

     FileSize,

     NumDaysBehind,

     ReloadedOn

FROM

[..\QVD\QVD_Monitor.qvd]

(qvd);

For each vFile in FileList('M:\Qlikview_IMO_Analytics\Dev - KK\QVD\*.qvd*')

     let vFileSize    = Alt(FileSize('$(vFile)'), 0);

     let vRecordCount = Alt(QvdNoOfRecords('$(vFile)'), 0);

     let vCreatedon   = Alt(FileTime('$(vFile)'), 0);

     let vDaysBehind =  num(Today()) - num(floor(vCreatedon));

 

  Concatenate   

     QVD_Monitor:

    

       LOAD

          '$(vFile)'      as FileName,

          $(vCreatedon)   as GeneratedOn,

          $(vRecordCount) as RecordCount,

          $(vFileSize)    as FileSize,

          $(vDaysBehind)  as NumDaysBehind,

           NOW()          AS ReloadedOn

          //RangeSum(Peek('FileCount'), 1) as Filenum    

     AUTOGENERATE(1)

     ;

Next 

STORE QVD_Monitor into [..QVD\QVD_Monitor.qvd];

end if

I used hat script and used pivot table to show the comparison..Thanks for your help

View solution in original post

2 Replies
marcus_sommer

You could scan your qvd-folder each day with a filelist-loop and load the meta-data like vFile, filetime(), qvdnoofrecords() and add those informations into a another qvd in an incremental approach. If you only want to compare today with yesterday it might be enough to store the number of records into a variable or you used both approaches.

Re: In context of For Each ... FileList(), how to make NoConcatenate

- Marcus

Anonymous
Not applicable
Author

IF  (isNull(qvdCreateTime('..\QVD\QVD_Monitor.qvd'))) THEN

For each vFile in FileList('M:\Qlikview_IMO_Analytics\Dev - KK\QVD\*.qvd*')

     let vFileSize    = Alt(FileSize('$(vFile)'), 0);

     let vRecordCount = Alt(QvdNoOfRecords('$(vFile)'), 0);

     let vCreatedon   = Alt(FileTime('$(vFile)'), 0);

     let vDaysBehind =  num(Today()) - num(floor(vCreatedon));

     

     QVD_Monitor:

    

       LOAD

          '$(vFile)'      as FileName,

          $(vCreatedon)   as GeneratedOn,

          $(vRecordCount) as RecordCount,

          $(vFileSize)    as FileSize,

          $(vDaysBehind)  as NumDaysBehind,

           NOW()          AS ReloadedOn

          //RangeSum(Peek('FileCount'), 1) as Filenum    

     AUTOGENERATE(1)

     ;

Next 

STORE QVD_Monitor into [..\QVD\QVD_Monitor.qvd];

ELSE

QVD_Monitor:

LOAD FileName,

     GeneratedOn,

     RecordCount,

     FileSize,

     NumDaysBehind,

     ReloadedOn

FROM

[..\QVD\QVD_Monitor.qvd]

(qvd);

For each vFile in FileList('M:\Qlikview_IMO_Analytics\Dev - KK\QVD\*.qvd*')

     let vFileSize    = Alt(FileSize('$(vFile)'), 0);

     let vRecordCount = Alt(QvdNoOfRecords('$(vFile)'), 0);

     let vCreatedon   = Alt(FileTime('$(vFile)'), 0);

     let vDaysBehind =  num(Today()) - num(floor(vCreatedon));

 

  Concatenate   

     QVD_Monitor:

    

       LOAD

          '$(vFile)'      as FileName,

          $(vCreatedon)   as GeneratedOn,

          $(vRecordCount) as RecordCount,

          $(vFileSize)    as FileSize,

          $(vDaysBehind)  as NumDaysBehind,

           NOW()          AS ReloadedOn

          //RangeSum(Peek('FileCount'), 1) as Filenum    

     AUTOGENERATE(1)

     ;

Next 

STORE QVD_Monitor into [..QVD\QVD_Monitor.qvd];

end if

I used hat script and used pivot table to show the comparison..Thanks for your help