5 Replies Latest reply: May 21, 2013 1:22 AM by Raghav Kumar RSS

    Minimum function through scripting?

    Raghav Kumar

      I have the following table after doing some work on the base data.

       

       

      PlaceWarehouseSourceFreight1Freight2Sum (Freight1, Freight2)
      P1WH1S110010110
      P1WH1S220020220
      P1WH2S120010210
      P2WH2S130020320
      P2WH2S240010410

       

       

      I want to add one column "Classification", which would give

           "L1" if the Sum column for a given Place is the MINIMUM among the available options.

           "L2" if the Sum column for a given Place is the 2nd MINIMUM and so on.

      I have showed the example for 2 Places.

       

      Something like this.

      PlaceWarehouseSourceFreight1Freight2Sum (Freight1, Freight2)Classification
      P1WH1S110010110L1
      P1WH1S220020220L3
      P1WH2S120010210L2
      P2WH2S130020320L1
      P2WH2S240010410L2

       

      I got the answer for doing this through expression, but I am looking for doing this through script.

      Kindly help.

       

       

      Thanks in advance.

        • Re: Minimum function through scripting?
          Rob Wunderlich

          How about this way (sample QVW attached).

           

          data:

          LOAD *,

                    Freight1 + Freight2 as TotalFreight

          ;

          LOAD * INLINE [

          Place          Warehouse          Source          Freight1          Freight2

          P1          WH1          S1          100          10          110

          P1          WH1          S2          200          20          220

          P1          WH2          S1          200          10          210

          P2          WH2          S1          300          20          320

          P2          WH2          S2          400          10          410

          ] (delimiter is '\t')

          ;

           

           

          data2:

          LOAD

                    *,

                    'L' & AutoNumber(TotalFreight, Place) as Classification

          RESIDENT data

          order by TotalFreight

          ;

          DROP TABLE data;          // Drop orginal table

           

          -Rob

          http://robwunderlich.com

          • Re: Minimum function through scripting?
            Sushil Kumar

            Please find attached

             

            HTH

            sushil