topic QVD Quality Management in QlikView

QVD Quality Management

Tue, 13 Mar 2012 10:47:08 GMT

Good Morning Community,

most of our QV Reports load from QVD Files.

While "producing" QVD files on the server (QV10), i sometimes encountered a Problem, which might lead to serious Problems in the reports that load from the QVD files.

In (the rare) case the server crashes while storing a qvd, there still is the (unifinished) QVD on the HDD, from which the other documents load data.

The unfinished QVD File is still readable, but it contains garbage data (because some of it is missing).

If documents load data from these QVD, they

- might miss the data (f.e. some fiels/months missing)

- work with the wrong data

- continie do distribute false / incomplete data

Is there a way to make sure, that the creation process of QVD file was finished, or to know if it was interrupted ?

the only information source i know so far, is the .log file of the .qvw file.

Is it possible to write a "flag" to the file as athe last step before completing the store process ?

if so, checking the flag before loading a QVD could prevent these problems

QVD Quality Management

jvitantonio — Tue, 13 Mar 2012 10:50:54 GMT

Hi,

You can use ScriptErroCount(). If it is > 0 then there were errors during script execution. You can store this value in a file and that would bne your flag. Check out the Help for more information.

QVD Quality Management

rbecher — Tue, 13 Mar 2012 11:10:38 GMT

Hi Alexander,

I would store a record into a log table (QVD) after each store command of the generated "business" QVD.

The loading application then could use this log table to iterate over all successful loaded QVDs.

- Ralf

QVD Quality Management

Wed, 21 Mar 2012 08:50:34 GMT

Hi Ralf,

this seems to be a very good idea. A "success.qvd", in which each application stores the "success" status of each creation of a qvd file.

After creating a .qvd every application would load the file (success.qvd), add a line with the

- time

- name of the script (QVW)

- name of the QVD

then store the QVD again. If the script crashes, it does not affect the success.qvd. There just is no record of the new qvd.

When using one of these QVD, the success.qvd has to be read in the script too.

The date in the success.qvd can be compared with the file creation date of the other QVD.

I will make a few tests and post the code here if it works like it should.

QVD Quality Management

rbecher — Wed, 21 Mar 2012 09:17:39 GMT

Hi Alexander,

yea, this was the idea, basically. But, if many QVW apps (the QVD generators) are loading simultaneously and would try to log into a shared single succes.qvd I would expect a concurrency problem.

If this is the case I would suggest that every QVW would write in a single successXXXX.qvd file per load. A frequently parallel running "watchdog" QVW app could then collect the records of all those successXXXX.qvd files (in a loop) to store this into a main succes.qvd file.

- Ralf

Re: QVD Quality Management

Wed, 21 Mar 2012 15:14:24 GMT

@ Ralf

I havent thought of that. It doesn mater much, if there are only few generators which are different in size (and timeconsumption) or if the generators are not running paralell, but in sequential order. In this case, the access times to the success.qvd wouldnt overlap.

But if many generators are used paralell which are equal in time consumtion, this can lead to a big problem.

The watchdog could be a simple join load

QVD_succs: // Make Henn
LOAD * INLINE [
   Q_ID, Q_Date, Q_Time, Q_Name
    0, 01.01.2012, 00:02, example.QVD
   ];
  
// STORE QVD_succs INTO $(vQVDStore)QVD_success.QVD; // Henn, runn once
  
QVD_success:
NoConcatenate LOAD 
 Q_Date, 
    Q_Time, 
    Q_Name
    //FileName() as File_Name
FROM
[$(vQVDStore)QVD_success.QVD]
(qvd);
QVD_success:
outer join LOAD
//Today()   as Q_Date,
Date(Now()) as Q_Date,
Time(Now()) as Q_Time,
AutoNumber(Q_Time&Q_Date) as Q_ID,
'example.QVD' as Q_Name
RESIDENT QVD_succs;
DROP table QVD_succs;
// RESTORE
STORE QVD_success INTO $(vQVDStore)QVD_success.QVD; // EGG

Re: QVD Quality Management

rbecher — Wed, 21 Mar 2012 20:41:10 GMT

Hi Alexander,

you don't need the "outer join". Just LOAD again the 2nd time (from) AUTOGENERATE 1, not from resident.

It will append exactly one row to the previous loaded rows.

- Ralf

PS: maybe use some variables to set values for the fields..

Re: QVD Quality Management

Thu, 22 Mar 2012 11:41:17 GMT

Using AUTOGENERATE 1 produces an error sometimes. The script simply stops to write new rows to the qvd.

outer join + resident works without errors, which is more important to me than nice looking code.

there is only 1 variable so far: the QVD name which is currently stored. everything else stays the same.

Which value should be replaced by a variable.

There is one last problem:

the usual method of generating ID is not working, RecNo, RowNo creates garbage data.

I used (num(date(Now()))) as quick and dirty solution, but there must be something better.

Next step: Aggregate all $(name)_success.qvd files.