Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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!

Tags (3)
2 Replies
Not applicable

Re: Manipulations with loops

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

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;