Script to get all empty fields from a QVD

[Enno86]

Hi Qlik Experts,

I am new to Qlik Sense and am happy with the community, as it provides lots of great feedback. My current issue, though, I wasn't able to resolve yet and seek for your input 🙂

Task: Load approx 150 QVD files with different amount of fields, as well as different amount of data. We want to find from 7.500 different fields, the ones which are empty, or have "dummy" data (e.g. "0000").

We were able to generate a table, which holds all fields of one QVD, but we can't get the "IsEmptyFlag" to work.

here is the current code snippet:

TBL: First 100 LOAD * FROM [lib://QlikData/XYZ.qvd] (qvd);

For f = 1 to 1//NoOfFields('TBL')
ListFelder:
Load
FieldName($(f), 'TBL') as Field
AutoGenerate 1;

Let vFieldName = Peek('Field', -1, 'ListFelder');

Next f;

What do I need to incorporate in the code to achieve my goal, or is there another way of achieving it?

[WaltShpuntoff]

What are you trying to achieve here? 

You can either investigate system fields. ex: create a table, and set the first dimension to $Field. Then create a measure FieldValueCount($Field). This will list all of the fields in your app, along with a count of how many values are in it.

The second approach is to load just the metadata from the QVD files. You can do this by opening them in the data load editor, and change the file format drop down to XML. This gets you to all of the metadata.

[Enno86]

Hi @WaltShpuntoff , first of all: thanks for your input.

I want to evaluate the different qvd files and want to check, which of the 7.500 columns are irrelevant, due to being "null", "0000", "0000000000" or whatever.

Your first hint helped me already to look for the fields, which have distinct values.

Would you also have a solution, for the disctinct values to be shown in another column?

Regarding the XML: When I choose the "XML" in my QLik Sense Enterprise, the list of files is just empty...as only QVDs are stored there-

[WaltShpuntoff]

1. Re: XML - open the QVD as a QVD and when the preview shows, THEN select XML

2. This is a little too general to address directly. Some things to consider - NullAsValue to make nulls selectable.

a really cool trick to to create a filter box with the dimension =[$(=$Field)]

It may say that it is invalid in the editor, but when you have one value selected for $Field, it will render out to the selected field name, giving you a kind of data browser.

[Enno86]

to 1: not sure how to do this...there is no preview in my data connection for the qvd-files. and in the load editor I don't know how to "go for" XML.

=[$(=$Field)] => this helps me to be quicker...but it would be cool to have at the end:

$Field ||  FieldValueCount($Field)  ||  String to Concat all values

[jpenuliar · Accepted solution]

Steve Dark has created this tool:  https://www.quickintelligence.co.uk/qlik-sense-data-profiler/ 
might prove useful for your requirements.