Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ?
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;