Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Frank,
I tried your solution, but that didn't seem to work for me . Do you have other suggestions?
Cheers
iSam
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.
Hi Frank,
I will upload a qvd/ qvw file ASP.
Cheers!
Carter
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
Hi Carter,
Is this what you're looking for?
gr.
Frank
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
You're welcome.