Manipulations with loops

Sun, 17 Nov 2013 17:52:07 GMT

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!

Re: Manipulations with loops

Mon, 18 Nov 2013 15:01:23 GMT

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

Mon, 18 Nov 2013 15:56:53 GMT

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;

topic Re: Manipulations with loops in QlikView

Manipulations with loops

Re: Manipulations with loops

Re: Manipulations with loops