Examining QVD metadata
Qlikview data files (extension .qvd, referred to as QVD files in this article) are used for intermediate data storage, history, incremental loads and so on. The file contains the full information of a single data table and the files are usually generated and consumed by the load script. The metadata is useful when developing and operating multitier Qlikview setups.What you may not know is that the qvd file contains extensive metadata for the table. This includes:
- The name of the qvw file that created the QVD file
- The version of Qlikview when the load script that created the QVD was run
- The time the file was created
- Metadata for the fields (name, number of unique values, data type, width, symbol table offset etc
- The number of rows in the table
- Lineage data, including data sources, SQL queries and connection data
Windows Batch "More"
A simple way to look at the metadata (as raw XML) is to use the Windows batch "more" command running in a console window. To set his up, you need a simple batch file. These are the steps to set this up:- Create a text file on your desktop using a text editor (notepad will do, although I prefer Notepad++)
- Add a single line: more "%1"
- Save the file.
This approach is simple, but not always the most convenient way to get the metadata you need.
Powershell Script
To simplify extracting the metadata and displaying it in a clear layout, I have created a Powershell script to extract the XML and unpack the XML elements. Powershell is a standard component of Windows since Windows XP/Server 2003.If you are not already using Powershell, create a scripts folder: eg C:\Scripts or C:\Users\Fred\Scripts. Download the script ps1 file and copy to the scripts folder you created.
qvdMeta.ps1
You can use this script using the following command line (type in CMD window):
>powershell C:\Scripts\qvdMeta.ps1 -source
You can add this to a batch file saved on your desktop:
@echo off
powershell C:\Scripts\qvdMeta.ps1 -source %1
Now drag the qvd file and drop it on this file to see the fie metadata:
QVD Meta data extractor V1
Source file C:\Data\QVDs\Balances.qvd
Created by C:\QVDeploy\QVDCreators\Balances.qvw
Qlikview build 12664
No of Rows 231 372
Table name BALANCES
Build time 2015-12-08 01:26:59
Fields:
EffDate 1 282 values
RelNo 389 values
Company 8 values
FundCode 29 values
ClientName 362 values
ClientBalance 9 855 values
XML lines 201 ; bytes = 6 499:
Note the script has been tested with QVD file produced by QV 11.20 up to SR13. I have not tested it with QVD files produced by Qlik Sense, or QV 12. It may need minor mods.
Open using Qlikview
If you are not interested in the metadata, but want to examine a qvd, you can read the qvd in Qlikview - just drag the qvd and drop on the QV icon on your desktop. This will create a load script. Now click File | Reload, and create some list boxes, and a table box for a quick view of the content.To view the metadata only, change the file type in the QV file preview to XML. Now you can load the XML into QV rather than the actual data.