10 Replies Latest reply: Nov 18, 2014 4:54 PM by Angel Serrano RSS

    improve data quality using qlikview? pls help!

      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 AFilling %Field Occurence in Source BFilling %Field Occurence in Source CFilling %
      Per Source201970100%64670100%100100%
      No zipcode3750018,57%1435022,19%3024,27%
      No CityName10,00%00%00%
      No Driverlicense282027,33%1015015,70%4542,72%
      No Education1501,39%4160644%1716,50%
      No Experience10,00%00%00%
      No travel Distance5740028,42%2355036,42%2524,27%
      Education1100000544,21%241250373,06%200194,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