QlikView App: QVD Meta Data Viewer

[stevedark]

There is a large amount of useful information that is stored in the header of QVD files.  This gives information about the data in the file, when it was created and which app created it.  This data is in an XML format and can be viewed in a text editor.

Being XML it can also be loaded in to QlikView and viewed there.

This blog post describes how this can be done and gives some background to the application uploaded here:

https://www.quickintelligence.co.uk/qlik-qvd-xml-header-viewer

The application enumerates around a number of libraries and reads the header information from each QVD in those libraries.  It persists the information it finds to a QVD and also creates a timestamped archive - so you can see how your QVDs are changing over time.  By default this is written to the same folder you are querying QVDs from.

If you have any questions regarding getting this app set up please post in the comments below.

There is a QlikView version of this application also, which you can find here:

Qlik Sense App: QVD Meta Data Viewer

Both versions of the app also include our popular Data Profiler, which you can find more information about here:

Qlik Sense App: Generic Data Profiler

I hope that you find the application useful.  You will find other applications that I have uploaded under my profile on QlikCommunity or on our Downloads page..

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

[sunny_talwar]

Steve this link https://www.quickintelligence.co.uk/qlik-qvd-xml-header-viewer isn't working. Taking to a page which shows this

[stevedark]

Wow Sunny, I knew you  were quick to pick up on new articles but that was incredibly swift!

The issue was due to a bit of a cyclic reference.  The blog post has links to the QlikView and Sense versions of the app, and the apps on Qlik Community reference the blog post.  I didn't want the blog to have broken links, so I created the Community entries first.

The links have now been placed into the blog post and this has been published.  I would be very pleased if you were the first to read it.

Many thanks,

Steve

[sunny_talwar]

This is great, it was always challenging to find out which qvw was used to create a qvd file. This would resolve that issue.

I created a straight table with

Dimension

File name

Expression

=SubField([File Created By], '\', -1)

and boom it gives me just what I needed....

Thanks Steve!!

[stevedark]

Thanks for the feedback Sunny.

[jayanttibhe]

This is Great Steve. I was working to create something like this for creation of the Extraction Layer and logging historical metadata for QVDs. Your solution is right what I needed.

Just one suggestion. If you can provide a flag in your "QVDFileRowsArchive.qvd"  which will enable to identify the latest QVD file. i.e. min(File Age) Per QVD created.

[stevedark]

Hi Jayant,

Glad you like the app.  The code is all there for you to amend, so go ahead and change it!

The changes you need to make are both on the Create Archive tab, add a new line to line 11, which reads like this:

[Number Of Records],

'Yes' as [Is Latest],

[File Name],

And then further on down add a line when the old records are being appended, on line 41, which reads:

[Number Of Records],

'No' as [Is Latest],

[File Name],

Your archive QVD will then have an Is Latest field with Yes/No on it.  The only downside of this approach is that it will stop the append of the old records being an Optimised QVD Load, but unless you have hundreds of QVDs this is unlikely to be a real problem.

To create the app which looks at the QVD over time you need to clone the one I have uploaded and change it to load from the archive QVD, then add the time controls.

Hope that makes sense?

Cheers,

Steve

[jayanttibhe]

Thanks Steve. Actually I was working on the same line. While reading the "QVDFileRowsArchive.qvd ";

I just added below mentioned code. And it worked the way I wished

Thanks again for this great work. This will really help us in the data profiling.

Latest_File_Flag:

Load
[QVD Name],
min([File Age Minutes]) as [File Age Minutes],
1 as Latest_QVD_Flag
FROM [QVDFileRowsArchive.qvd] (qvd);

Group by [QVD Name]

[stevedark]

Hi Jayant,

You do have a risk there that you could have two QVDs with the same timestamp, one being the latest and the other not.  You could create a composite key though, in fact, if you change the date format on the QVDKey to 'YYYYMMDDhhmmss' then that could serve that purpose.  Or the code I posted above is (I think) foolproof.

For profiling the content of your QVDs you may want to look at this app:

QlikView App - Generic Data Profiler

Steve

[jayanttibhe]

Thanks Steve.I had thought on it but its very unlikely to have 2 exact same (named)QVDs generated at the same time unless 2 diff parallel tasks are not creating those. So I think thats fine. Howevr, your solution works well in my requirement.