Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kkoyyalamudi
Contributor

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
kkoyyalamudi
Contributor

Re: QVD Comparison

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
MVP & Luminary
MVP & Luminary

Re: QVD Comparison

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

kkoyyalamudi
Contributor

Re: QVD Comparison

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