For each .... with Peek giving null

[adr]

Hi, 

I have a problem with Pick which I use in a loop. I get the right result during the first loop and then pick always gives a null result. Can someone see the problem ? Here's my script  : 

load CaseNumber 

from PAIEMENTS ; 

for each a in fieldvaluelist('CaseNumber')

load

CaseNumber , 

DATE,

Balance,

if(isnull(previous(Balance)),Balance, Balance+peek('Result')) as Result,

if(isnull(peek('Result')),'Y','N') as IsnullPeek

resident PAIEMENTS  where CaseNumber  = $(a); 

next a ;

Here's the result  : 

CaseNumber	DATE	Balance	Result	Expected Result	IsnullPeek
114847	08/10/2004	-  34,20 €	-  34,20 €	-  34,20 €	Y
114847	28/10/2004	75,24 €	41,04 €	41,04 €	N
114847	16/02/2005	-   41,04 €	0  €	0   €	N
114557	08/10/2004	-  32,08 €	- 32,08 €	-       32,08 €	Y
114557	23/11/2004	64,11 €	0   €	32,03 €	Y
114557	16/02/2005	-   32,03 €	0   €	-      32,03 €	Y

[Taoufiq_Zarra]

@adr 

You don't need for each here.
to send you the correct script, can you confirm something,

why ?

in this line the expected is 0

114847

16/02/2005

-   41,04 €

0  €

0   €

N

and in this line is -32.03 and not 0

114557

16/02/2005

-   32,03 €

0   €

-      32,03 €

Y

we can get both with two scripts but I need your confirmation.

[adr]

In this whe're getting 0  :   - 41,04 € + 41,04  = 0

114847

16/02/2005

-   41,04 €

0  €

0   €

N

and in this line is -32.03  + 32.03   = 0  -> It was a mistake the result should be 0 not -32.03

114557

16/02/2005

-   32,03 €

0   €

-      32,03 €

Y

[Taoufiq_Zarra · Accepted solution]

@adr  OK très bien

the script :

Datatmp:

LOAD * INLINE [
    CaseNumber, DATE, Balance 
    114847, 08/10/2004,-34.20
    114847, 28/10/2004,75.24
    114847, 16/02/2005,-41.04
    114557, 08/10/2004,-32.08
    114557, 23/11/2004,64.11
    114557, 16/02/2005,-32.03
];


Data:
noconcatenate
load rowno() as ID,* resident Datatmp order by CaseNumber DESC;
drop table Datatmp;

output:
noconcatenate
load *,
if(ID=1,num(round((Balance),0.01),'# ##0,00 €;-# ##0,00 €'),num(round(Balance+Peek('result'),0.01),'# ##0,00 €;-# ##0,00 €')) as result


resident Data;


drop table Data;

output :