topic Re: improve data quality using qlikview? pls help! in QlikView

improve data quality using qlikview? pls help!

Tue, 24 Jul 2012 13:47:46 GMT

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 A	Filling %	Field Occurence in Source B	Filling %	Field Occurence in Source C	Filling %
Per Source	201970	100%	64670	100%	100	100%
No zipcode	37500	18,57%	14350	22,19%	30	24,27%
No CityName	1	0,00%	0	0%	0	0%
No Driverlicense	2820	27,33%	10150	15,70%	45	42,72%
No Education	150	1,39%	4160	644%	17	16,50%
No Experience	1	0,00%	0	0%	0	0%
No travel Distance	57400	28,42%	23550	36,42%	25	24,27%
Education	1100000	544,21%	241250	373,06%	200	194,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

Re: improve data quality using qlikview? pls help!

Tue, 24 Jul 2012 14:20:58 GMT

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

Re: improve data quality using qlikview? pls help!

Tue, 24 Jul 2012 14:46:18 GMT

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

Re: improve data quality using qlikview? pls help!

Tue, 24 Jul 2012 16:02:20 GMT

Hi Frank,

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

Cheers

iSam

Re: improve data quality using qlikview? pls help!

Tue, 24 Jul 2012 16:11:45 GMT

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.

Re: improve data quality using qlikview? pls help!

Wed, 25 Jul 2012 10:06:30 GMT

Hi Frank,

I will upload a qvd/ qvw file ASP.

Cheers!

Carter

Re: improve data quality using qlikview? pls help!

Wed, 25 Jul 2012 11:54:12 GMT

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

Re: improve data quality using qlikview? pls help!

Wed, 25 Jul 2012 12:48:31 GMT

Hi Carter,

Is this what you're looking for?

gr.

Frank

Re: improve data quality using qlikview? pls help!

Wed, 25 Jul 2012 13:05:26 GMT

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

Re: improve data quality using qlikview? pls help!

Wed, 25 Jul 2012 13:10:27 GMT

You're welcome.

Re: improve data quality using qlikview? pls help!

Tue, 18 Nov 2014 21:54:35 GMT

This is quality!, thanks!