Qlik Sense 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 Sense 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.

You will need to configure the app to find your libraries by editing the load script, just list the libraries in this INLINE load:

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:

QlikView 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/

[jpjust]

Hi Steve,

I am trying to bring the app name by using monitor_apps_REST_app API but after adding the below code on the "Getstats" script tab,  the script runs forever. Can you please check and let me know if there is anything wrong. id_u4 is the app ID.

Temp_QVDHeader:
LOAD
$(iID) as JoinID,
QvBuildNo as QvBuildNo,
CreatorDoc as [File Created By],
TableName as [Table Name],
NoOfRecords as [Number Of Records]
FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]);

RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u4",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u3",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"availabilityStatus",
"__KEY_root",
(SELECT
"userId",
"userDirectory",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"name" AS "name_u2",
"__FK_stream"
FROM "stream" FK "__FK_stream"),
(SELECT
"id" AS "id_u7",
"@Value" AS "@Value_u0",
"name" AS "name_tag",
"__FK_tags"
FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u0")
FROM JSON (wrap on) "root" PK "__KEY_root";

App:
LOAD
[id_u4] AS [File Created By],
[name_u3] AS [App Name QRS]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

drop table RestConnectorMasterTable;

[stevedark]

Not sure what is going on there @jpjust . First thought though is that you are doing a second REST call before dropping the temporary table from the first. The way the connector names the fields there could be a number that are identical between the temporary tables, and that would cause a massive synthetic key.

I would create a list of applications in a separate loader and then persist that to QVD. You can then just load the GUID and application name into a mapping table in your QVD Header app.

Having QVDs as a staging layer is pretty much always a good idea.

Hope that helps.

Steve

[jpjust]

Thanks Steve, I will modify the script.

Also, I thought of checking with you on this.

Is it possible to give top level folder instead of giving sub folders?

Like EU_QVD_FOLDER/TECH/UK/Apple, so the script can go and scan qvd's in sub folders as well.

Otherwise my entries is going to be huge.

tmpLibs:
LOAD
Lib
INLINE [
Lib

EU_QVD_FOLDER/TECH/UK/Apple/BITables
EU_QVD_FOLDER/TECH/NL/Apple/CR

];

[stevedark]

Hi @jpjust 

Certainly possible. Here is some (very old) code from a QlikView app that I have used that enumerates around files in sub folders.

https://www.quickintelligence.co.uk/FolderBrowserQlikView.qvs

I've not published it on community as an app, partly as I got different results based on the platform it was run on and partly as the code was largely based on a Community post by Rob Wunderlich so I didn't want to push it as my own.

You will need to modify the code to make it work in Sense and with the QVD bits, but hopefully it will give you a point in the right direction.

Good luck!

Steve

[jpjust]

Thanks much Steve, I will take a look.

[Huxleys]

Hi Steve - this app is just what I've been looking for. Similarly to the above post, is there a simple way to enter the top level folder, in my case the data connection is named QlikviewQVD, and have the app scan through sub folders also:

tmpLibs:
LOAD
Lib
INLINE [
Lib
QlikviewQVD
];

I may be in the same position as Jpjust whereby I have many subfolders and folder within folders etc.

[stevedark]

Hi @Huxleys 

See the script I posted a couple of months back. Modifying that should work for you. @jpjust may be able to share his code if he got it working?

Steve