topic Data Profiling checks in Load Script in QlikView

Data Profiling checks in Load Script

Wed, 03 Aug 2016 18:30:35 GMT

Hi,

Inspired by the data profile application QlikView App - Generic Data Profiler

- Thanks stevedark -

I wanted to create the "Profile of Data" section in my load scrip so that the table is more dynamic.

My script says:

Load *,

minstring($(=minstring($Field))) as 'Minimum Value'

Resident [Population]

(I already have a data table called population in my script)

When I run the script I get:

Error in expression:

MinString takes 1 parameter

Load *,

minstring() as 'Minimum Value'

Resident [Population]

It changes my expression with the $field value. Am I not allowed to use system fields in the script? Could someone help me?

Thanks!

Laila

Re: Data Profiling checks in Load Script

stevedark — Thu, 04 Aug 2016 19:34:30 GMT

Hi Laila,

As far as I am aware neither $Field or $(= will work in a load script.

What you can do though is enumerate around an array of tables and of fields. Using this, and some variable expansion you can get a table created that has some of the stats that I have in my QlikView Data Profiler.

Just copy and paste this script to a tab at the end of your load script:

for iTab = 0 to NoOfTables() - 1

let vTab = TableName(iTab);

for iField = 1 to NoOfFields('$(vTab)')

let vField = FieldName(iField, '$(vTab)');

FieldProfile:

LOAD

1 as ProfileCount,

DocumentName() as [Profile Document],

'$(vTab)' as [Profile Table],

'$(vField)' as [Profile Field],

minstring([$(vField)]) as [Profile Min],

maxstring([$(vField)]) as [Profile Max],

count(distinct [$(vField)]) as [Profile Uniques]

RESIDENT [$(vTab)]

;

next

let iTab =;

let vTab =;

let iField =;

let vField =;

You can obviously add more aggregations over and above the three that I have put in.

I would expect that this could slow down a load script more than a little on a large document, I have just created it over a small test app. I would also expect that you will get better performance from my original app.

What you will notice though is that I have added the app name into the table, you could add path also (DocumentPath). If you then stored this profile table to QVD you could potentially load the profile from all of your applications into a single app and do analysis across apps - how many apps does the field Customer appear in, for instance.

By the way, if you want a full description of how my original profiler was put together you can read it here:

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

Hope that helps you out,

Steve

Re: Data Profiling checks in Load Script

Thu, 04 Aug 2016 21:01:50 GMT

Hey Steve,

Thanks so much for your input. I added the script and follow your logic, but now I am seeing 2 tables: Field Profile and Population, and when I click either of the tables and any of the fields, my table (I cloned your original table and put in the few new fields in) is not updating. I am seeing a dash next to each field like the screenshot below:

Do I have to name the variable somewhere in variable overview? I don't understand these 2 lines:

for iTab = 0 to NoOfTables() - 1

let vTab = TableName(iTab);

Are they defining the tablenames? How? Do I have to somehow link the two tables?

Also, the end goal I am hoping to get to is basically your exact Profile Table but the ability to click "Yes" next to has non alpha numeric for example, and see a list of the cells in that field that have a special character in it. Do you think this is achievable?

Thanks again, and hoping we can figure something out together!

All the best,

Laila

Re: Data Profiling checks in Load Script

stevedark — Thu, 04 Aug 2016 22:16:13 GMT

Hi Laila,

What my new script does is create a whole new table (FieldProfile) that can be used for the profiling objects. To use it you will need to set things up a bit differently on the profiling tab.

The simplest way of using the new table is to add a table box to the app, select the FieldProfile table and add all fields as dimensions, use the button to sort into Load Order. On the Presentation tab, add a Dropdown Select on every field. This will then give you the profile for every field in one go, and you can select from the Table and Field columns to view the detail for individual fields.

The iTab value is a counter, which increments as the script looks at each table in the data model, and vTab is the name of the table currently being looked at. If you run the script in Debug with the Step option you can hover over these variables and see what is going on.

I've expanded my original example and attach it here. You can see on the new tab how the has non alpha selection work - it picks fields which have non alpha in.

To pick a field and then make a selection in that field of all non alpha values would be a bit different. You would need to have a button with an action to make the selection, and then that would need to send the code in to select on an expression. I will have to have a look at that another time...

Hope the app makes sense and is useful.

Steve

Re: Data Profiling checks in Load Script

Fri, 05 Aug 2016 13:52:13 GMT

Hi Steve,

That is awesome. Thank you! The one thing I was hoping for was when the Has Non Alpha button or field in the row was selected, the actual cells in the field that have a special character would be shown.

I am picturing a table that looks like a "select * from Tablename" type of table in SQL, that would update as the Qlik script is executed. - a detailed table that can pinpoint the cells.

Let me know if that is confusing. Do you think that type of thing would exist in Qlik?

All the best,

Laila

Re: Data Profiling checks in Load Script

stevedark — Sat, 06 Aug 2016 07:55:57 GMT

Hi Laila,

As I mentioned in my previous reply the best way to select rows is with a selection in the front end, rather than pre-calculating it in the load script.

I've now added a couple of buttons to the front page to do this for two selection types. Please find attached.

This is a nice addition, and one I will most likely incorporate into my standard profiler - thanks for the idea!

Steve

Re: Data Profiling checks in Load Script

Mon, 08 Aug 2016 13:23:21 GMT

Thank you so much, Steve! This is perfect. Just what I was hoping for!

You are welcome for the idea- hope it comes in handy!

Re: Data Profiling checks in Load Script

stevedark — Mon, 08 Aug 2016 20:58:31 GMT

Glad it has given you what you are looking for.

You can subscribe to my blog to be notified when I put out new changes to the app or publish other tutorials etc.

Cheers,
Steve