Replace missing Values

Sun, 06 Jan 2013 11:04:52 GMT

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

(100+50+23)/3

How can we do this

Re: Replace missing Values

nagaiank — Sun, 06 Jan 2013 13:10:17 GMT

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.