topic Re: Null and data completeness percentage in QlikView

Null and data completeness percentage

deepakkrish — Sat, 16 Nov 2024 02:12:56 GMT

Hi Everyone ,

I have two tables Table A and Table B , my requirement is I have to calculate How many null values Table A and Table B have and Data completeness percentage for both the tables .

below is my tables,

Table A			Table B
name	address	phone	salary	contact	pin
A	null	789	null	null	123456
b	null	null	null	null	456
c	BG	null	78909	null	789

expected output

Tables	null	completeness
Table A	4	55%
Table B	5	44%

Below is my expression :

Null :
if($Table='Table A', sum(if(IsNull(name),1,0))+sum(if(IsNull(Address),1,0))+sum(if(IsNull(phone),1,0)))

completeness:
if($Table='Table A', (1-sum(if(IsNull(name),1,0))+sum(if(IsNull(Address),1,0))+sum(if(IsNull(phone),1,0)) /
sum(if(not IsNull(name),1,0))+sum(if(not IsNull(Address),1,0))+sum(if(not IsNull(phone),1,0))))*100

I got null values correctly , please help me to get completeness by percentage..

Thanks,

Krish.

Re: Null and data completeness percentage

StarinieriG — Wed, 16 Oct 2019 16:00:50 GMT

try with

1- (your null expression / ($Rows*Max($FieldNo)))

Re: Null and data completeness percentage

deepakkrish — Wed, 16 Oct 2019 16:55:49 GMT

Thanks for your reply .. this is not working

Re: Null and data completeness percentage

asinha1991 — Wed, 16 Oct 2019 17:30:05 GMT

there might be a better way but you can do this

(count(name)+count(Address)+count(phone))/NoOfRows('TableA')*3

3 is number of fields which you already know..

also for counting null there is a direct function NullCount(FieldName) you don't need to use sum with if

Re: Null and data completeness percentage

Brett_Bleess — Tue, 22 Oct 2019 21:17:56 GMT

Krish, did the last post help you get things working? If so, do not forget to use the Accept as Solution button on the post to give credit for the help and let others know that worked. If you are still needing help, leave an update for us.

Regards,
Brett