Replace missing Values

[#1]

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

[nagaiank · Accepted solution]

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.