Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
try with
1- (your null expression / ($Rows*Max($FieldNo)))
Thanks for your reply .. this is not working
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
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