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.
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 %|
|No travel Distance||57400||28,42%||23550||36,42%||25||24,27%|
'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.