topic Re: Reading another QVDs load date in QlikView

Reading another QVDs load date

chrismtb — Mon, 15 May 2017 14:49:37 GMT

As part of a reporting suite of dashboards I want to build a simple yet functional view for non admin users that shows the last successful load dates of these QVDs.

Is there an easy way to pull in to my new view the last successful load date of a document?

Thanks

Re: Reading another QVDs load date

Frank_Hartmann — Mon, 15 May 2017 15:07:47 GMT

may be this:

QvdCreateTime (qvd_file_name)

QvdCreateTime(filename)

Returns the XML-header time stamp from a QVD file if any (otherwise NULL).

The filename is the name of a QVD file, if necessary including path.

Examples:

QvdCreateTime('MyFile.qvd')

QvdCreateTime('C:\MyDir\MyFile.qvd')

hope this helps!

Re: Reading another QVDs load date

Peter_Cammaert — Mon, 15 May 2017 15:55:03 GMT

Are you talking about

QVDs (data files)? Then use Frank's solution
QVWs (documents)? Then load the XML header using a LOAD FROM abc.qvw and look for the <LastReloadTimeISO> tag value. There are similar tags in different timezones as well.

Best,

Peter

Re: Reading another QVDs load date

chrismtb — Wed, 17 May 2017 11:00:13 GMT

Hi Peter - I tried your method... however having never loaded an XML header before I had to go elsewhere on the forum to work out how to do it!

Just using the load wizard I am able to see the XML document summary but can't for the life of me spot the field (or anything similar) that you called out in your reply.

Am I looking in the wrong place or using an incorrect method (either is equally as likely) or is it possible I just cant get to the information?

Thanks

Chris

Re: Reading another QVDs load date

Frank_Hartmann — Wed, 17 May 2017 11:08:41 GMT

like this:

Re: Reading another QVDs load date

chrismtb — Wed, 17 May 2017 11:52:56 GMT

Thanks Frank - that proves it does exist anyway so that's a start!

Now I know its there I just have to work out how I write the load script to get that information into a dashboard.

I did try something simple like:

LOAD

LastReloadTimeISO,

UtcLastReloadTimeISO

FROM C:\MyDir\MyFile.qvw (Xmlsimple, Table is [LoadTime]);

Based on what I had seen when messing about with the load wizard and <document summary> xml info.

This however does not seem to work - any ideas?

Apologies if this is a bit like spoon feeding a baby!

Re: Reading another QVDs load date

Frank_Hartmann — Wed, 17 May 2017 12:12:54 GMT

try like this:

Re: Reading another QVDs load date

goro2010 — Wed, 17 May 2017 13:01:39 GMT

Love It!

Re: Reading another QVDs load date

Peter_Cammaert — Thu, 18 May 2017 07:34:38 GMT

You can't go wrong with these personalised trainings!

Re: Reading another QVDs load date

chrismtb — Thu, 18 May 2017 14:15:46 GMT

Brilliant, it works like a charm... I made one small change though, from the second statement I removed the * from the load statement (see below).

This is because when I multiplied this up for several dashboards there seemed to be a huge amount of data retention upon saving the dashboard... removing the * meant that the code still had the desired effect and the footprint of the file was hugely reduced.

LOAD [@1:n]

FROM

C:\MyDir\MyFile.qvw

(fix, codepage is 1252, no eof);

load //*,

distinct(TextBetween([@1:n],'<LastReloadTimeISO>','</LastReloadTimeISO>')) as ReloadDate,

Resident 1; DROP table 1;

There are probably much more effective ways for cleaning these things up but it got me the desired outcome.

Thanks

Chris