Show ReloadTime of a QVD in another QVW

[buddhabang]

Hi,

Can anyone please help me on this issue: 

I have couple of QVDs for numerous tables which are created one table at a time. Now this morning, a lot reports have failed that were dependent on the successful reload of these QVDs. 

Due to the large amount of tables, all of these are auto-reloaded daily, but if one fails and the subsequent report starts to load, then fails, my work clients can see that their report have loaded today, yet the data they want was not correct.

My question is this, is there anyway of finding out the reloadtime of these QVDs in each my reports, so we can tell at a glance that data will be up to date  or not.

Please help.

Thanks.

FYI: Trying reloadtime() in the QVD creators is not good for me, i need that info to be imported in my reports.

Thank you so much.

[Jason_Michaelides · Accepted solution]

You could have a separate QVD file that stores all the reload times, maybe.  E.g., in each QVD generator, after every STORE command try something like the following (untested):

IF FileSize('QVDReloads.qvd')>0 THEN

     LOAD * FROM QVDReloads.qvd(qvd);

     CONCATENATE (QVDReloads)

     LOAD

          QVDFile,

          Now()     AS     ReloadDateTime

     INLINE [

     QVDFile

     QVDFile123,

     ];    

ELSE

    QVDReloads:

     LOAD

          QVDFile,

          Now()     AS     ReloadDateTime

     INLINE [

     QVDFile

     QVDFile123,

     ]; 

END IF;

STORE QVDReloads INTO QVDReloads.qvd;

DROP TABLE QVDReloads;

Then, in each of your QVWs, load this QVD and get the maximum relaodDateTime for your required QVD files.

Hope this helps,

Jason

[buddhabang]

Thank you so much Jason, i will be testing this tomorrow morning as almost done now.

Will come back to you, cheers mate.

[rwunderlich]

In the script of your report qvw you can create a table of qvdnames and Qvd Create times:

LOAD

     'myqvd.qvd' as QvdName,

     QvdCreateTime('myqvd.qvd') as QvdTime

AUTOGENERATE 1;

-Rob

http://robwunderlich.com

[Jason_Michaelides]

That's somewhat simpler than my solution, Rob!  Could you use this function to query all QVD files in one go (assuming they were all in the same folder)?

Jason

[rwunderlich]

Yes, you can loop through all the QVDs in a directory and subdirectory. You can also use the other Qvd*() functions like QvdNoOfRecords() to uild up something of a Dictionary of your Qvds.

-Rob

[StefanBackstrand]

In my persuit of using QlikView on everything, I want to suggest to use it on the script log that gets created during the reload. I sometimes use that to break down a script file into rows that I can then turn up in a line chart showing me exactly where my script spends significant portions of time.

The script log however, is a bit tiresome in its format, and also looks different between Desktop and Publisher. But for those into substring(), it's a real adventure!

[buddhabang]

Thanks guys, i've only had time to go through this today. Jason, your solution worked better for me although it was more long winded but it worked, Rob i've tried yours first but maybe i was missing something, but no data showed up when i tried it.

Thanks for all your help, Stefan will bare that in mind.

[buddhabang]

By The way guys, looking at the code again, we tought why not just use Now() as Reloadtime_TABLE in all all of the QVD creators, whcih actually works perfectly, less scripting too.

Just reload the QVD which will now contain the time along with other fields.

Super guys.