Percentage of inputted fields by total number of fields.

[mahnoor1279]

I want to calculate the percentage of   (not null values of columns for each person / total no of columns)

Name	email	country	cell no
sarah	sarah1278@gmail.com	pakistan	342353463
adam			13254643
zain		india	1324322

[vinieme12 · Accepted solution]

as below

temp:
load Name,email,country,cellno
,  rangesum(if(len(Name),0,1),if(len(email),0,1),if(len(country),0,1),if(len(cellno),0,1)) as nullValues

inline [
Name,email,country,cellno
sarah,sarah1278@gmail.com,pakistan,342353463
adam,,,13254643
zain,,india,1324322
];

Main:
load *
,num((NoOfFields('temp')-2-nullValues) / (NoOfFields('temp')-2),'#,###.##%') as PercentageOfValues
Resident temp;
Drop table temp;

Exit script;

you can also avoid loading the Main table and calculate the same in front end using just the nullValues field as below

(3-sum(nullValues)) / 3