How add a field to table while looping the records because depending of previous values to reset

[kristien]

Hello,

I want to add a field "running saldo" to each record in a table. Depending of some conditions the running saldo must restart at zero, therefore it must be done in the load script and i cannot use functions like rangesum in the chart

I see how I can loop through the records and calculate the running saldo. But I don't see how i can add this field to the appropriate record in my table.

My script looks something like this:

FactTemp:

…

(FactTemp is the full table with all fields loaded, and where i finally want to add the "running saldo" to all records)

AddCommandInProgressTemp:

Load
article
, documentDate
, document
, quantity
Resident FactTemp
Order by article, documentDate;

//(it's important that the records are read in the correct sort order)

Let vNoOfRows = NoOfRows('FactTemp');
Set vSaldo = 0;
Set vNewSaldo = 0;
Set vPrevArticle = '';

FOR i=0 to $(vNoOfRows)-1

  SET vQuantity=Peek('[quantity]',$(i),'AddCommandInProgress');  //(quantities can be positive or negative)
  SET vArticle=Peek('[article]',$(i),'AddCommandInProgress');
  SET vDocumentDate=Peek('[documentDate]',$(i),'AddCommandInProgress');
  SET vDocNr=Peek('[document]',$(i),'AddCommandInProgress');

 //(for each new article, running saldo must turn to zero)

  If $( vArticle) <>  vPrevArticle then
           SET vSaldo = 0;
 End If

  Let vNewSaldo = vSaldo + vQuantity;

//(running saldo cannot be negative. When negative restart at zero)

  If vNewSaldo < 0   Then
           Set vNewSaldo = 0
EndIf

  Let vPrevArticle = $( vArticle);

  let vSaldo = vNewSaldo;

(the purpuse of AddFields is to build a table where the new field NewSaldo is added and to finally add them to the original table. But like this i get a ScriptError : "Unexpected Token: NewSaldo..."

AddFields:
Left Join(FactTemp)

LOAD
$( vArticle) as article
,$( vDocumentDate) as documentDate
,$(vDocNr) as document
,$(vNewSaldo) as [NewSaldo];

NEXT i;

Can someone explain how this can be done?

Tx

[Gysbert_Wassenaar]

Generally a running total is added using the Previous and Peek function with a regular LOAD. Loops are not necessary.

UnsortedFacts:
LOAD
    Article,
    Date,
    Quantity
FROM
   ...source... ;

Facts:
NOCONCATENATE LOAD
     Article,
     Date,
     Quantity,
     If(Previous(Article)=Article, RangeMax(0,RangeSum(Quantity, Peek(RunningSaldo))),Quantity) as RunningSaldo
RESIDENT
     UnsortedFacts
ORDER BY
     Article,
     Date
     ;

DROP TABLE UnsortedFacts;

[kristien]

Thanks, this helped me,  I'm back on the right track now