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

kristien — Sat, 16 Nov 2024 21:04:43 GMT

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?

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

Gysbert_Wassenaar — Wed, 10 Apr 2019 14:15:49 GMT

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;

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

kristien — Wed, 10 Apr 2019 15:46:15 GMT

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

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

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

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

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