Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How To Read Meta Data From A QlikView QVD
QVD files are the backbone of any well designed QlikView application. You store your data in there and read it out again – simple. But, did you know that the QVD header holds some useful meta-data? Did you know that could be loaded into QlikView for analysis? No? Well, read on.
QVD Files Are XML Files
If you have ever been inquisitive enough to open a QVD file in a text editor you will have seen that the file starts with a chunk of XML. If you haven’t done this previously, why not do it now? As you look through the headers you will see some basic information, such as the time the QVD was created. Further on in the header is the number of rows in the file. You may well be aware that these values can be referred to using a number of functions, for example:
QvdNoOfRecords('c:\QlikView\Data\MyQVD.qvd'); QvdNoOfFields('c:\QlikView\Data\MyQVD.qvd'); Also in the header of the file you will find information on the field structure of the QVD and the lineage of the QVD (the initial SELECT or LOAD statements that went to build the QVD. All in all very interesting to look at in a text editor, but there are no functions to retrieve these things.
Beyond this, the QVD turns into binary for the storing of the data itself.
Retrieving The XML From A QVD
You may be aware that you can load data from an XML file into QlikView. So, it stands to follow that you can load the XML from your QVD into a table. And indeed you can.
When you click the Table Files button in QlikView and select a QVD, QlikView automatically changes to load the QVD data and shows you the content:
Just underneath the QVD radio button is a selection for XML. When you click this a different view of the QVD is shown:
You will see that there are tables you can select for header information, field information and lineage. Field information for a QVD is shown above, you can click on the other tables to see the content of these.
Clicking Finish will insert the code to load from the XML headers into your load script. The script for the field information looks like this:
QvdFields: LOAD FieldName, BitOffset, BitWidth, Bias, NoOfSymbols, [NumberFormat/Type] as Type, [NumberFormat/nDec] as nDec, [NumberFormat/UseThou] as UseThou FROM [.\Data\MyQVD.qvd] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]); When you Save and Reload you then have a table showing all the fields in your QVD.
Loading From Multiple Files
This gives us a simple way of getting some QVD information into a QlikView document. But what if we want to view information for a whole folder full of QVDs? Well, we can simply enumerate around them, in a similar fashion as we did with CSV’s in the post How To Convert Drop Folder Files to QVD. Enumerating is done with a for / next loop on a FileListstatement. For each file, we want to grab the name and add it as a field. The code (after a bit of additional tidying) then looks like this:
for each vFile in FileList('.\Data\*.qvd') let vFileName = mid(vFile, index(vFile, '\', -1) + 1, 99); QvdFields: LOAD 1 as FieldCount, '$(vFileName)' as [File Name], FieldName as [Field Name], BitOffset as [Bit Offset], BitWidth as [Bit Width], Bias as [Bias], NoOfSymbols as [No Of Symbols], [NumberFormat/Type] as Type, [NumberFormat/nDec] as Dec, [NumberFormat/UseThou] as UseThou FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]); next If you also want to add in some header information, such as number of rows, you can add another statement within the loop to pull in those values:
QvdTableHeader: LOAD 1 as QVDCount, '$(vFileName)' as [File Name], QvBuildNo as [QV Build No], CreatorDoc as [QVD Creator], CreateUtcTime as [Time Created], SourceFileSize as [Source File Size], TableName as [Table Name], RecordByteSize as [Record Byte Size], NoOfRecords as [Number Of Records] FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]); For completion you could also pull in the lineage table for each file. You will note that the tables will all associate on the File Name field, as we keep this the same between tables.
With these statements in place you can then create a number of different charts and tables over the data. You can use the resulting app to answer a whole host of questions about your data layer, such as:
- Which QVDs have the most rows?
- Which QVDs have the most fields?
- Which field name is most prevalent across QVDs?
- What QVD creator app created the Customers QVD?
- What on earth did I call the QVD I created from the Balances spreadsheet?
Taking It Further
A recent use case I had for profiling all the QVDs in an implementation was for a server migration. After a period of parallel running we needed to ensure that the data in all of the QVDs was the same on the server being retired to the one being migrated to. By putting an additional loop around these statements to look in two locations, naming the locations in each table and using a composite key (of location and file name); I created an app that profiled QVDs from both servers. A few simple straight tables later and I was able to say with confidence that the QVDs had the same rows on both servers.
As well as being interesting for yourself, you may find that you can turn the application into a useful piece of living documentation for your users. They can find where fields reside in QVDs, particularly useful if you are going to allow them to self serve in Sense, for example.
I hope you find this post useful and it points you towards new ways of making the most of your QVDs.
