2 Replies Latest reply: Oct 19, 2017 1:47 AM by VIJAY VIRA RSS

    Peek and allocate units

    Mark Graham

      Hi all,

       

      I'm working on a requirement to allocate units based on my available stock:

       

        

      OrderitemReqAble to fill
      1A1010
      2A205
      3B1510
      4C20

      20

       

        

       

       

      itemAvailable
      A15
      B10
      C

      20

       

       

      Any help is highly appreciated!

        • Re: Peek and allocate units
          VIJAY VIRA

          Hi Mark,

           

          See if following will help.

           

           

          OrderitemReq
          1A10
          2A20
          3B5
          4C20
          5C5
          6B15
          7D10

           

           

           

          itemAvailable
          A15
          B10
          C20

           

          Fill.JPG

           

          NoConcatenate

          OrderTableTmp:

          LOAD Order,

               item,

               Req

          FROM

          Fill.xlsx

          (ooxml, embedded labels, table is Order);

           

           

          NoConcatenate

          AvailableTableTmp:

          LOAD item,

               Available

          FROM

          Fill.xlsx

          (ooxml, embedded labels, table is Available);

           

           

          NoConcatenate

          OrderCountTableTmp:

          LOAD item,

               Count(DISTINCT Order) as OrderCount

          Resident OrderTableTmp

          Group by item

          Order By item, Order;

           

           

          LET vNoOfRowsAvailable = NoOfRows('AvailableTableTmp');

          LET vNoOfRowsOrderCount = NoOfRows('OrderCountTableTmp');

           

           

          IF $(vNoOfRowsAvailable) > $(vNoOfRowsOrderCount) then

           

           

          NoConcatenate

          AvailableTable:

          LOAD item,

               Available

          Resident AvailableTableTmp;

          Left Join

          LOAD item,

               OrderCount

          Resident OrderCountTableTmp;

           

           

          ELSEIF $(vNoOfRowsOrderCount) >= $(vNoOfRowsAvailable) then

           

           

          NoConcatenate

          AvailableTable:

          LOAD item,

               OrderCount

          Resident OrderCountTableTmp;

          Left Join

          LOAD item,

               Available

          Resident AvailableTableTmp;

           

          ENDIF;

           

           

          DROP Table AvailableTableTmp;

          DROP Table OrderCountTableTmp;

           

           

          IF $(vNoOfRowsAvailable) > $(vNoOfRowsOrderCount) then

           

           

          NoConcatenate

          OrderTable:

          LOAD item,

          Available,

          OrderCount

          Resident AvailableTableTmp;

          Left Join

          LOAD item,

               Order,

               Req

          Resident OrderTableTmp;

           

           

          ELSEIF $(vNoOfRowsOrderCount) >= $(vNoOfRowsAvailable) then

           

           

          NoConcatenate

          OrderTable:

          LOAD item,

               Order,

               Req

          Resident OrderTableTmp;

          Left Join

          LOAD item,

          Available,

          OrderCount

          Resident AvailableTable;

           

          ENDIF;

           

           

          DROP Table OrderTableTmp;

          DROP Table AvailableTable;

           

           

          LET vNoOfRows = NoOfRows('OrderTable');

          LET vStoreItem = 0; // Null();

          LET vAbelToFill = 0;

           

           

          FOR i = 0 to $(vNoOfRows) - 1

           

           

          LET vItem = Peek('item', $(i), 'OrderTable');

          LET vOrder = Peek('Order', $(i), 'OrderTable');

          LET vReq = Peek('Req', $(i), 'OrderTable');

          LET vAvailable = Peek('Available', $(i), 'OrderTable');

          LET vOrderCount = Peek('OrderCount', $(i), 'OrderTable');

           

          IF vItem <> vStoreItem then

          LET vStoreItem = vItem;

          LET vAbelToFill = 0;

          LET vStoreAvailable = vAvailable;

          ENDIF;

           

          IF vReq <= vStoreAvailable then

          LET vAbelToFill = vReq;

          LET vStoreAvailable = vStoreAvailable - vAbelToFill;

          ELSEIF vReq > vStoreAvailable then

          LET vAbelToFill = vStoreAvailable;

          LET vStoreAvailable = vStoreAvailable - vAbelToFill;

          ENDIF;

           

           

          ProcessTable:

          LOAD *

          Inline [

          item, Order, Req, AbelToFill

          "$(vItem)", "$(vOrder)", "$(vReq)", "$(vAbelToFill)"

          ];

           

           

           

          NEXT i;

           

           

          DROP Table OrderTable;

          • Re: Peek and allocate units
            Varun Prakash Paulraj

            Try the below script

             

            T1:

            LOAD * INLINE  [

            item, Available

                A, 15

                B, 10

                C, 20

            ];

            join

            LOAD * INLINE [

                Order, item, Req

                1, A, 10

                2, A, 20

                3, B, 15

                4, C, 20

            ];

             

             

            T2:

            Load *, if(Previous(item) = item, Available- previous(Req), Available) as InQTY

            Resident T1 order by item, Req;

            Drop table T1;

             

             

             

             

            T3:

            load *,

            if(InQTY >Req, Req, InQTY) as [Able to Fill] Resident T2;

            Drop table T2;