What is the best way to create a data catalog for QVDs?

[Jaden]

Hi All,

We are realizing that our end users want to have more access to our qvds to create their own apps in Qlik Sense. We currently have quite a few qvds (200-300ish) and they are somewhat organized (in folders in the file system). I have been researching for the best way to come up with a sort of data catalog for our users so that they can easily know what data they need to include. I know the data fairly well, but I cannot sit down with every user, every time they want to create an app. That's why I am trying to come up with an alternative solution. This will also help us to be more organized in general.

Any suggestions are much appreciated. Thank you in advance.

[rwunderlich]

There are a number of vendor products that can create a catalog of QVD info, including Qlik's Catalog product. I suppose the answer depends on your use cases, budget and whether you will want to go beyond QVDs. 

If your needs are fairly simple, such as "which QVD(s) have a field named Sales?", you can build your own Qlik Sense app to scan QVDs and collect metadata. And then build some charts/search from the data model.

Here's a working sample script to get you started if you want to try the DIY approach. 

Sub ListQvds(_dir)
For Each _file in filelist('$(_dir)' &  '/*.qvd');
	// Get metadata using the builtin Qvd* functions. 
    // Alternatively we could read the QVD file as XML. 
    // see the Tags example below. 
	Files:
	Load 
		'$(_dir)' as Directory,
		'$(_file)' as [File Path],
        QvdTableName('$(_file)') as TableName,
        QvdCreatetime('$(_file)') as CreateTime,
        QvdNoOfRecords('$(_file)') as RecordCount
	AutoGenerate 1
    ;
    For _fieldNo = 1 to Alt(QvdNoOfFields('$(_file)'), 0)
    	Let vFieldname = QvdFieldName('$(_file)', $(_fieldNo));
        Let vFieldKey = '$(_file)' & '|' & '$(vFieldname)';
		Fields:
        Load
        	'$(vFieldKey)' as Key_Field,
            '$(_file)' as [File Path],
            QvdFieldName('$(_file)', $(_fieldNo)) as FieldName
         AutoGenerate 1
         ;
         // Show how we can read directly from the QVD XML Header as well
         Tags:
         LOAD
         	'$(vFieldKey)' as Key_Field,
             String%Table as Tag
         FROM [$(_file)]
         (XmlSimple, table is [QvdTableHeader/Fields/QvdFieldHeader/Tags/String]);
    Next _fieldNo
    
Next _file
For Each _subdir in dirlist( '$(_dir)' & '\*' )
		CALL ListQvds('$(_subdir)')
	Next _subdir;
End Sub

Call ListQvds('lib://qlikdata')  //**** The folder connection where we start scanning ****

Autonumber 'Key_*';

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Jaden]

Rob,

Thank you for this suggestion. I finally had some time to try and set this up and it works great. I was able to create a quick pivot table with all of our QVDs.

My question now is:

Is there a way to create a sample data table that shows a preview of the data once you select a QVD?

For example:

I select the 'AtCapacity' QVD. What's the best way to show a preview (5 or 10 rows of sample data from that QVD)? Attached below is a screenshot for reference.

[Jaden]

For instance, I have this script in now:

Set vFolder = 'lib://QlikSense_QVD/Scheduling';

for each File in filelist(vFolder& '\*.qvd')

first 2 load * from [$(File)] (qvd);

next File

This does load in the data for the folder, but there's not a way to join it with the script above.

Thank you.

[rwunderlich]

You'll need to add the same filename as a keyfield to link back to the other data.

first 2 load *,
  '$(File)' as [File Path],
from [$(File)] (qvd);

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com