Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: improve data quality using qlikview? pls help!

Hi Carter,

Is this what you're looking for?

gr.

Frank

10 Replies
Not applicable

Re: improve data quality using qlikview? pls help!

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

Re: improve data quality using qlikview? pls help!

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

Re: improve data quality using qlikview? pls help!

Hi Frank,

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

Cheers

iSam

Not applicable

Re: improve data quality using qlikview? pls help!

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

Re: improve data quality using qlikview? pls help!

Hi Frank,

I will upload a qvd/ qvw file ASP.

Cheers!

Carter

Not applicable

Re: improve data quality using qlikview? pls help!

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

Re: improve data quality using qlikview? pls help!

Hi Carter,

Is this what you're looking for?

gr.

Frank

Not applicable

Re: improve data quality using qlikview? pls help!

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

Re: improve data quality using qlikview? pls help!

You're welcome.

Community Browser