Whenever you are given a new data set to consume in QlikView the first thing you will want to do is take a ‘quick look’ at what information it is you have been given. To enable me to do this quickly I have a few QlikView objects I can copy and paste into any document to allow me to do this. You can download these from QlikCommunity or read on to find out how to create your own.

Please note that there is a Qlik Sense version of this document here.

System Tables

There are a few System tables in all QlikView documents that allow you to interrogate what your data model looks like.  To see these you need to enable the Show System Fields checkbox.  You will find this on the Fields tab of the Sheet Properties dialog.  When this check box is ticked some new fields appear, prefixed with a dollar symbol.  The two that are used in this QlikView document are $Field and $Table.  This is the same way you can expose fields that you have marked with HidePrefix.  These new fields can be used in any document as they are, but we are going to do a bit more with them.  Be warned if you are using them however that they are data islands – and as such you need to be careful mixing them with fields from the rest of your data model in expressions.

Picking A Single Field

When profiling our data we are going to look at a single field from the data model at a time.  The field to be looked at is the first ‘available’ field alphabetically.  If no fields are selected this is across all fields, or a single field can be selected from the $Field list box.

List of fields in the data model

In order to go from many possible values in the $Field list to just the first we can use a bit of Set Analysis to obtain that value:

=minstring($Field)

This expression is used in the caption of a list box that shows each distinct value in the selected field.  The listbox itself has an expression as the data source rather than a single field.  The expressions that you can have in a list box are much like the Calculated Dimensions in charts.  If the result of the expression is not a field name then selections can sometimes behave a bit strange, but here we are just deriving a field name so all is well.  The code required is:

=[$(=minstring($Field))]

You will note it is similar to the caption – we are just expanding it with a dollar sign expansion and encapsulating it in square brackets (in case there is a space in our field name).  The result looks like a standard list box:

QlikView List Box

One of the useful things about this list box is that it allows you to make selections that are reflected through the rest of the document.  In this way you can have a tab where a user can see a full list of values in any field and select them.

Finding Some Statistics

Once we have found the field we want and have a distinct list of the field values we will want to find out a bit more about the field.  The most basic thing to do is to count how many times each of the values occurs in the data:

Frequency of values in the field

Whilst you should generally try to avoid them, the most generic way to find the frequency of each of the values is to do a Count, so the Expression for the above chart is:

=count([$(=minstring($Field))])

The dimension is the same as for the list box.  If you have built the data model and you can rely on their being a counter field (which will be more efficient) then you could do a sum of the Counter field instead.

Digging A Bit Deeper

Counting values is just one of the ways in which you can find out more about the contents of the field.   I’ve picked a few simple measures that give you a bit of a flavour about what is in there.  The values I have chosen for my profiler are:

Field Statistics Box

The expressions are all relatively straight forward, just with the field name inserted into them with a dollar sign expansion, for instance Max Length is calculated with this expression:

=max(len([$(=minstring($Field))]))

I tend to stick with the eleven metrics above, but when Barry Harmsen over at the QlikFix showed me the document he had created for the same purpose he had many many more expressions in there.  Feel free to add more to the list above – but there will be a trade off with performance as you add expressions.

Download The QlikView Data Profiler

From the details given in this article you should be able to build your own data profiling page, with components you can drop onto any QlikView document.  If you would prefer a pre-canned QlikView Download then you can get this from QlikCommunity – where I have uploaded my own document:

https://community.qlik.com/t5/Member-Articles/QlikView-App-Generic-Data-Profiler/ta-p/1483687

If you like the app then please acknowledge this with a positive rating on the Community.  Please also see the other documents I have uploaded under my QlikCommunity account.

As always I appreciate any feedback – so feel free to use the comment box below.