Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to calculate the percentage of (not null values of columns for each person / total no of columns)
Name | country | cell no | |
sarah | sarah1278@gmail.com | pakistan | 342353463 |
adam | 13254643 | ||
zain | india | 1324322 |
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
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