Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

improve data quality using qlikview? pls help!

Hi Guys,

We would like to improve our data quality of a certain database. Therefore I’ve being given the task to create a table that shows how many records of fields are filled and how many are records remained empty.

This table is a based on a model that has one fact table and two other tables that are connected to the fact table.  The three tables are:

Resume (facttable) This table contains the field Source.
ResumeDetail
Education

The idea is to create a similar table like the one below:


Field Occurence in Source AFilling %Field Occurence in Source BFilling %Field Occurence in Source CFilling %
Per Source201970100%64670100%100100%
No zipcode3750018,57%1435022,19%3024,27%
No CityName10,00%00%00%
No Driverlicense282027,33%1015015,70%4542,72%
No Education1501,39%4160644%1716,50%
No Experience10,00%00%00%
No travel Distance5740028,42%2355036,42%2524,27%
Education1100000544,21%241250373,06%200194,17%

'No zipcode' shows the count the records that don't have a zip from the ResumeDetail table. This goes for all of the other fields.

Since the field 'Source'  is always filled, the percentage of filling 100%. As far as the other fields, they are optional and thus sometimes left unfilled. Since users have usually more than one education the amount of ‘education records’ are a lot. The sample above shows that education has more than 1100000 records which in percentage is 544% that are filled.

How can I create such data table in Qlikview?

I tried using flags, such as: if(zipcode='', 1,0) as Flag_zip, I later on use it in a straight table and sum it. That shows me the amount of records that don't have a zip code. However I need it to get in line with 'no zipcode', but I can achieve that like the above example (which is a crosstable from Excel).

I fruther tried creating a pivot table without any dimension, just using expression. That results in the screenshot below:

Can I create such cross table above in Qlikview? Can someone please post a simple example like the table above?


Much Help appreciated!

Please let me know if it's unclear.

Regards

Carter

1 Solution

Accepted Solutions
Not applicable
Author

Hi Carter,

Is this what you're looking for?

gr.

Frank

View solution in original post

10 Replies
Not applicable
Author

He Carter,

This should be possible in qlikview.

Try making a field wich contains all the dimension names, and one field with a numeric representation of those dimension names.

Create a straight table with the dimension names and the numeric value of those dimension (hide this dimension).

Then create an expression with something like this.

if(num_dimension=1, sum(Flag_zip),

if(num_dimension=2,sum(Flag_cityname),

if(num_dimesnion=3,sum(Flag_driverlicense)

)))

etc.....

I used an numeric_dimension because it's less work to type and it reduces the chance of typo's.

Hope this helps.

gr.

Frank

Not applicable
Author

He Frank,

Thanks for your help!

I will give it a try and let you know if it works.

I'll see if I can upload a sample of your suggestion.

Cheers!

Carter

Not applicable
Author

Hi Frank,

I tried your solution, but that didn't seem to work for me . Do you have other suggestions?

Cheers

iSam

Not applicable
Author

What exactly didn't work?

If you could upload a qvd file with some (fake) data in it, it might be easier to help you.

Not applicable
Author

Hi Frank,

I will upload a qvd/ qvw file ASP.

Cheers!

Carter

Not applicable
Author

Hi Frank,

I've created some fake data using Excel. In order to generate more records, I concatenated the same file a couple of times.

I've attached both Qlikview and the QVD File. How you can help.

Cheers!

Carter

Not applicable
Author

Hi Carter,

Is this what you're looking for?

gr.

Frank

Not applicable
Author

Hi Frank,

This is exaclty what I was looking for!!! Thanks man!

I really appriciate your help!!

Now I understand what you ment by create a number for each field and how that's done.

Cheers!

Carter

Not applicable
Author

You're welcome.