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?
