1 Reply Latest reply: Jan 6, 2013 8:10 AM by Nagaian Krishnamoorthy RSS

    Replace missing Values

      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

        • Re: Replace missing Values
          Nagaian Krishnamoorthy

          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.