Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Lets assume that we have the following table generated when the script is run.
satisfaction score time score service score
---------------------- ---------------- -----------------------
100 98 88
50 - 100
- 55 80
23 - 100
now i want to replace the missing value with avg(of the column) so that
satisfaction score
------------------------------
100
50
(100+50+23)/3
23
How can we do this
The following script seems to work.
Data:
load * Inline [
satisfaction score,time score, service score
100,98,88
50,,100
,55,80
23,,100
];
Data2:
load Avg([satisfaction score]) as ASS, Avg([time score]) as ATS Resident Data;
Join (Data) LOAD * Resident Data2;
Drop Table Data2;
UpdatedData:
LOAD If(Len([satisfaction score])>0,[satisfaction score], ASS) as [satiscation score]
,If(Len([time score]),[time score], ATS) as [time score]
,[service score]
Resident Data;
Drop Table Data;
Hope this helps.
The following script seems to work.
Data:
load * Inline [
satisfaction score,time score, service score
100,98,88
50,,100
,55,80
23,,100
];
Data2:
load Avg([satisfaction score]) as ASS, Avg([time score]) as ATS Resident Data;
Join (Data) LOAD * Resident Data2;
Drop Table Data2;
UpdatedData:
LOAD If(Len([satisfaction score])>0,[satisfaction score], ASS) as [satiscation score]
,If(Len([time score]),[time score], ATS) as [time score]
,[service score]
Resident Data;
Drop Table Data;
Hope this helps.