Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks, this helped me, I'm back on the right track now