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.
Solved! Go to Solution.
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.
I used an numeric_dimension because it's less work to type and it reduces the chance of typo's.
Hope this helps.
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.
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.
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.