One of our most popular Qlik Downloads is the Data Profiler we produced for use with QlikView. This post shows how you can produce a Qlik Sense Data Profiler using the same techniques.
The QlikView Data Profiler
Back in 2013, I put out a blog post about the Data Profiler that we created and use in house for getting a flavour for the data values that exist in a new set of data. We always use this in the initial stages of building a solution, and it generally ended up on a tab at the back of finished QlikView applications. As I’m sure you can imagine we have now ported this across to Qlik Sense to use in applications there.
This blog post covers how you can build the same thing in Sense, but it skips over some of the technical details of the expressions. If you want to get under the bonnet of this solution, you may want to read the previous post, here:
https://www.quickintelligence.co.uk/qlikview-data-profiler/
Bringing To A New Environment
To create the same functionality in Sense you will first need to create a Master Dimension called Data Profiler Dimension. This dimension contains a list of all fields from the system and has the following code:
=[$(=minstring($Field))]If you are using the September 2017 release or above, you can also add the =minstring($Field) in the Label field. This will change column labels and dimensions to be the current selected value. If you, like me, have been waiting over two years for this it’s quite a relief to be able to do this.
The next step is to start creating the objects on the page, starting with a list of fields. Drag a new Filter Pane onto the page (or double click Filter Pane). You can not select the dimension you require from the Dimension button, or find it on the Field List on the left hand side – as it is a hidden system field.
You need to open the Data option on the Filter Pane properties and set the Field to $Field, the Title should be set to Data Profiler Fields.
The filter box for tables is built in the same way, with values being set to $Table and Data Profiler Tables respectively.
The next list box flexes to show the values in the Master Dimension we created earlier. You can add this to the page just by double clicking the Master Dimension Data Profiler Dimension from the left hand panel. If you are in the July 2017 release or above you can then change the title to also be =minstring($Field).
And now we get to putting in the bar chart which shows us how many times each value appears in the data model. Double click the bar chart icon to add this to the page. The dimension is again our Master Dimension Data Profiler Dimension. And the measure for the chart requires the following:
count([$(=minstring($Field))])You can then tidy the chart up by changing the name of the Measure to Occurrences, setting the title for the chart to =’Occurrences of Values in $(=minstring($Field))’ and rotating the chart to have horizontal bars (under Appearance and Presentation), changing the sort order to descending by Measure (do this by dragging Occurrences above the Dimension under Sort) and removing the axis labels (under Appearance and X-Axis & Y-Axis).
The chart should then end up looking a bit like this:
The final object is a table that gives some stats about the field that has been chosen. This table has a number of measures, all of which use the [$(=minstring($Field))] field-name in the expression. The complete list of expressions I use for this are here.
| Measure | Expression |
|---|---|
| Field Name | =minstring($Field) |
| Min Value | minstring( [$(=minstring($Field))]) |
| Max Value | maxstring( [$(=minstring($Field))]) |
| Max Length | max(len([$(=minstring($Field))])) |
| Min Length | min(len([$(=minstring($Field))])) |
| Has Alpha | if(max(len(keepchar(lower([$(=minstring($Field))]), ‘abcdefghijklmnopqrstuvwxyz’))) > 0, ‘Yes’, ‘No’) |
| Has Number | if(max(len(keepchar(lower([$(=minstring($Field))]), ‘0123456789’))) > 0, ‘Yes’, ‘No’) |
| Has Non Alhpa Num | if(max(len(keepchar(lower([$(=minstring($Field))]), ‘0123456789’))) > 0, ‘Yes’, ‘No’) |
| Distinct Values | num(count(DISTINCT [$(=minstring($Field))]), ‘#,##0’) & ‘ of ‘ & num(count([$(=minstring($Field))]), ‘#,##0’) & ‘ (‘ & num(count(DISTINCT [$(=minstring($Field))]) / count([$(=minstring($Field))]), ‘#,##0.0%’) & ‘ unique)’ |
| Null Values | sum(if(isnull( [$(=minstring($Field))]), 1, 0)) |
| Blank Values | sum(if(trim([$(=minstring($Field))]) = ”, 1, 0)) |
Once this has been done you will want to arrange the objects that you have created into a sensible formation, and you should have a page that looks a bit like this:
Now you can pick any table and field combo from your data set and see some basic information about that table and field. For those who have come from QlikView (or even if you haven’t) you will need to remember that the default action on clicking a value in a filter pane is to add to the selection. This is not what you will want to do with these filter panes. To get the QlikView style functionality of a click clearing the current selection just hold down Ctrl when you click.
Copy and Paste Objects
One of the beauties of the QlikView implementation of this profiler was that you could go to the profiler page in one app, do a quick Ctrl+A then Ctrl+C to copy all objects, then simply Ctrl+V into a new app. It’s not quite as simple in Sense. You will need to create the Master Dimension in each app that you use the profiler, and each object needs to be copied one at a time. Copying and pasting is not possible between different versions of Sense (Desktop, Cloud and Enterprrise) but you can always Import a QVF file from one environment to another to allow that copy to work.
If you would like to download a QVF file with the objects and code in, this can be downloaded from Qlik Community here:
https://community.qlik.com/docs/DOC-19124
You will also find it on our list of downloadable Qlik examples (along with many other apps).
With the previous version of the app, there was quite a lot of discussion on both the blog post and the Qlik Community regarding how it could be improved and how it works. You may want to check those places out also, or start a new conversation below.
The post Qlik Sense Data Profiler appeared first on .
