Manipulations with loops

[#1]

Hi,

I have the following scenario:

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!

[#2]

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 ?

[#3]

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;