2 Replies Latest reply: Nov 18, 2013 10:56 AM by A B RSS

    Manipulations with loops

      Hi,

       

      I have the following scenario:

       

      loops.PNG.png

       

      There are delivery transactions, which have to be balanced By receipt transactions. The order is FIFO.

      I need to record the balances as displayed on the right:

      So I loaded the data in 2 tables: Delivery and Receipt.

       

      I figure the only way to do it is with nested loops, where the Receipt is on the outer loop, and the Delivery on the inside loop.

      I have 3 scenarios:

      - the Quantity of the Receipt  is equal, less then or bigger then the current delivery transaction.

      So the Next is determined accordingly.

      In the inner loop, I have to concatenate a row in a new table for each Delivery Balance.

      Is it possible?

      But I’m not sure my approach to achieve the balance table is correct.

       

      Would appreciate some tips and guidelines.

       

      Thanks!

        • Re: Manipulations with loops
          Srikanth P

          Can you please post some more sample data and the requirement is not clear ?

           

          Please elaborate the requirement ? I believe we can do with out loops as well ?

            • Re: Manipulations with loops

              Hi,

              I ended up doing the following script. looks messy, but does the job.

              I could not find how to do it without using loops.

               

               

              Delivery:
              LOAD
              DocID as DelDocID,
              Date as Del_Date,
              Quantity as Del_Qty
              FROM
              $(Path)Trans.xls
              (
              biff, embedded labels, table is [Sheet1$])
              Where Type ='Delivery' ;

              Receive:
              LOAD
              DocID as Rec_DocID,
              Date as Rec_Date,
              Quantity as Rec_Qty
              FROM
              $(Path)Trans.xls
              (
              biff, embedded labels, table is [Sheet1$])
              Where Type = 'Receipt';

              LET vDelivNoOfRows = NoOfRows('Delivery');
              LET vReceiveNoOfRows = NoOfRows('Receive');


              SET IRecNum = 1;
              SET JRecNum = 1;
              SET Counter = 0;

              SET ICount = Null;

              SET vNextDelDoc = 'T'; //--a flag to control moving to a new doc


              FOR IRecNum =  1 to $(vReceiveNoOfRows) //Start Iterating on Receive docs

              lET ICount = IRecNum-1;


              LET VRecvDate = Peek ('Rec_Date',$(ICount),'Receive');
              LET VRecvQty = Peek ('Rec_Qty',$(ICount),'Receive');
              LET VrecDocID = Peek ('Rec_DocID',$(ICount),'Receive');

              FOR JRecNum$(Counter)+1 to $(vDelivNoOfRows) //Locate a Delivery doc - FIFO by date
                  
              //--Each next, start from the position stored at Counter
                  
              IF vNextDelDoc = 'T' then //--read the next Delivery doc
                         LET VDelDocID = Peek ('DelDocID',$(Counter) ,'Delivery');
              LET VDelDate = Peek ('Del_Date',$(Counter) ,'Delivery');
              LET VDelQty = Peek ('Del_Qty',$(Counter) ,'Delivery');
              ENDIF;


              LET  vBalance = VRecvQty -VDelQty// -- diff between qty in docs
                        

              Concatenate (Bal_Table)
              LOAD
              $(VDelDocID) as BalID,
              if($(vBalance)>=0,0, fabs($(vBalance))) as DelBal, //--no balance left to receive in the current doc
                                   $(VRecvDate) - $(VDelDate) as BalDays,
              AutoGenerate(1) ;

              IF vBalance < 0 then
              LET VDelQty = fabs($(vBalance));
              SET vNextDelDoc = 'F';
              EXIT For; //--still left to be received - move to next receive doc

              ELSEIF   vBalance = 0 then
              LET Counter = $(Counter) + 1;
              SET vNextDelDoc = 'T';
              EXIT For;       ; //--Move position to next delivery doc & next receive doc, 
                              ELSE //--there is bigger qty in the receive doc then the current delivery doc. Move to next delivery doc
                                             
              LET VRecvQty = vBalance;
              LET Counter = $(Counter) + 1;
              SET vNextDelDoc = 'T';

              ENDIF;

              NEXT;

              NEXT;