article QlikView App - Generic Data Profiler in Member Articles

QlikView App - Generic Data Profiler

stevedark — Sat, 08 Apr 2023 13:17:03 GMT

Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of viewing the data that is in that application. To enable me to do this I have put together a page of objects that lists all tables and fields in the data model and then gives outline information about any selected field. These objects can be copied and pasted into any QlikView application to view the data model of that document.

I have documented how this document works and the reasons why you might use it in a blog post here:

https://www.quickintelligence.co.uk/qlikview-data-profiler/

Please see the blog post for further details on using this document.

There is now a Qlik Sense version of this app, which you can find on Qlik Community here:

Qlik Sense App: Generic Data Profiler

I hope that you find it useful. You will find other applications that I have uploaded under my profile on QlikCommunity, or on our Downloads Page.

Steve

https://www.quickintelligence.co.uk/blog/

‎

PLEASE NOTE: The 'With Mask' version of the file includes an experimental tab that may or may not work well on large data sets. Please see comment below for details. If you are not sure which to download go for DataProfiler.qvw. Thanks!

Re: QlikView App - Generic Data Profiler

rustyfishbones — Thu, 17 Oct 2013 20:18:31 GMT

Thanks Steve, another gem from you!

Re: QlikView App - Generic Data Profiler

struniger — Fri, 18 Oct 2013 06:36:55 GMT

Thanks!

Re: QlikView App - Generic Data Profiler

mikkeltaylor — Fri, 18 Oct 2013 12:44:57 GMT

Cheers Steve, I've already found it very useful as I didn't know you could read in tables from wikipedia/web pages so easily!

Re: QlikView App - Generic Data Profiler

stevedark — Fri, 18 Oct 2013 14:12:31 GMT

Hi Michael, I use web data quite often when seeding demonstration apps, it's my next choice after INLINE and AUTOGENERATE. I forget that it might not be that obvious to others that you can do this.

I created a video on loading from a web page a few years back:

h ttp://www.quickintelligence.co.uk/loading-data-from-a-web-page/

Steve

Re: QlikView App - Generic Data Profiler

Anonymous — Fri, 18 Oct 2013 14:39:24 GMT

Thanks Steve: This offers a world of possibilities.

Re: QlikView App - Generic Data Profiler

mikkeltaylor — Mon, 21 Oct 2013 10:33:11 GMT

Thanks Steve, the video proved very useful as I was getting mixed results just changing the url, as I didn't even think about using the wizard.

Re: QlikView App - Generic Data Profiler

MK_QSL — Wed, 15 Jan 2014 17:20:03 GMT

Nice post. Something new which I never ever thought.

Re: QlikView App - Generic Data Profiler

stevedark — Wed, 15 Jan 2014 20:30:10 GMT

Thanks Manish and Massimo for your comments.

Re: QlikView App - Generic Data Profiler

Thu, 16 Jan 2014 05:58:11 GMT

Thanks Steve..Its very useful and it will reduce our testing time for sure..

Re: QlikView App - Generic Data Profiler

Marcio_Campestrini — Wed, 12 Feb 2014 10:13:03 GMT

Thanks for the app!

Re: QlikView App - Generic Data Profiler

Thu, 26 Jun 2014 16:57:54 GMT

thank you. I really like this. was very simple to copy over to my applications. One recommendation to add to the stats box. the actual count, there is only the distinct count, I added the count above the distinct count. Helped me to see if a value in one field was in many records or just a few so I could quickly determine if it is a one-off entry error or a bigger problem.

Re: QlikView App - Generic Data Profiler

stevedark — Fri, 27 Jun 2014 11:38:49 GMT

Hi Debbie, I usually show a row count in the caption bar of objects like that. Typically by summing a counter I add to each table. That was not done here as I wanted it generic and not dependant on anything done in the data model. Adding it in as a new value is a good idea.

Re: QlikView App - Generic Data Profiler

DavidFoster1 — Mon, 21 Jul 2014 15:08:38 GMT

Hi Steve

this is a great app. One thing I have seen in commercial data profiling tools are data-masks. e.g. 2345.55 would have a mask of 999.99 while Steve123 would have a mask of Aaaaa123. Having masks provide a great method for spotting potentially bad data when data is expected to conform to a certain mask or set of masks (e.g. UK postcodes, US ZipCodes, Email addresses, etc)

I was thinking that you could achieve the desired effect with a replace function, but then realised you would need at least 66 replace layers to replace both cases of the alphabet (just basic western characters and numerics).

Can you think of a cleverer approach?

Re: QlikView App - Generic Data Profiler

mikkeltaylor — Mon, 21 Jul 2014 15:19:37 GMT

This would be a great addition. We have a DQ tool from Informatica which we use to show 'masking', but would love to see this in the data profiler so that we can use it more widely.

Re: QlikView App - Generic Data Profiler

stevedark — Mon, 21 Jul 2014 20:48:16 GMT

Hi David / Mike,

I couldn't get to quite the mask you were after, in the time I had available, but I have come up with something that I think is equally if not more useful.

The mask is built from counts of each type of character, Upper Case, Lower Case, Number and Symbol (spaces are ignored).

So for example;

28.5% would be n3 s2.

Hello World would be A2 a8.

AbC 23*&6 would be A2 a1 n3 s2

You get the picture. I'm deriving this by setting up variables (in an overcomplicated way - I should have typed them) for each of the types. A number of subtractions of a purgechar version of the LEN vs. the full LEN are then performed to derive the code.

Being short snappy masks there will be far fewer distinct masks and therefore strange values will stand out. For instance, UK postcodes would all be A4 n3 or thereabouts.

Rather than posting all the code I will post the app to this thread. It will invariably be moderated for a while though...

If I think of a way to come up with Aaaa111, but I can't think of a way without a loop - which would not be good news (or very easy to do). I'm not even sure how performant this solution will be on a large data set.

Steve

Re: QlikView App - Generic Data Profiler

stevedark — Mon, 21 Jul 2014 20:53:40 GMT

Hi Debbie,

When adding in the mask for the above comment I also tweaked the distinct count code to read thus:

num(count(DISTINCT [$(=minstring($Field))]), '#,##0') & ' of ' &

num(count([$(=minstring($Field))]), '#,##0') & ' ('

& num(count(DISTINCT [$(=minstring($Field))]) / count([$(=minstring($Field))]), '#,##0.0%') &

' unique)'

This gives the current distinct count, plus the count of rows, and how distinct the values in that column are.

Hope that's helpful.

Steve

Re: QlikView App - Generic Data Profiler

DavidFoster1 — Tue, 29 Jul 2014 13:19:23 GMT

I think you are right about not being able to do this on the front end. It would need character-by-character parsing in the script.

Re: QlikView App - Generic Data Profiler

stevedark — Tue, 29 Jul 2014 13:27:12 GMT

In the example I put together, the translation of values into masks is done in the front end (as a calculated dimension). This is why I suspect it would be very slow over a larger set of data.

Re: QlikView App - Generic Data Profiler

stevedark — Sat, 06 Feb 2016 21:42:47 GMT

Hi Saran,

Glad you have found it useful. I tend to copy the objects onto just about every app I create.

Cheers,

Steve