Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Manipulations with loops


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.


2 Replies
Not applicable

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 ?

Not applicable


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

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

DocID as DelDocID,
Date as Del_Date,
Quantity as Del_Qty
biff, embedded labels, table is [Sheet1$])
Where Type ='Delivery' ;

DocID as Rec_DocID,
Date as Rec_Date,
Quantity as Rec_Qty
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');

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

Concatenate (Bal_Table)
$(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';