Peek & IsNull

[tomhovens]

I have the following script, but still the IsNull function doesn't work perfectly in combination with the Peek function. Who could help me auto

LOAD*,
Artikelnr &'|'& Num(Datum) as Product_x_DateID;

NULLASVALUE *;
SET Nullvalue ='';

Vov:

SQL SELECT Aantal-nieuw,
Aantal-oud,
Artikelnr,
Datum,
Magazijnnr,
Volgnummer
FROM VOV_Voorraad_verslag WHERE magazijnnr=1 AND Artikelnr =405015;

TempArtikel_x_Datum:
LOAD Distinct Artikelnr Resident Vov;

Join(TempArtikel_x_Datum)
LOAD Date(RecNo()+$(vMinDate)) as Datum AutoGenerate today() - vMinDate;

Concatenate(Vov)
LOAD* Where not Exists( Product_x_DateID );
LOAD Artikelnr, Datum,
Artikelnr &'|'&Num(Datum) as Product_x_DateID
Resident TempArtikel_x_Datum;

Voorraadverslag:
NoConcatenate
LOAD Artikelnr, Datum,
If(Artikelnr=Peek(Artikelnr) and IsNull([Aantal-oud]), Peek ([Aantal-oud]),
[Aantal-oud]) as Balance,
Magazijnnr
Resident Vov
Order By Artikelnr, Datum;

DROP Table Vov, TempArtikel_x_Datum;

[tomhovens]

Marc,

this is wat I need

Artikelnr	Datum	Aantal-oud	Nieuw
405015	19-Dec-14	4	4.00
405015	18-Dec-14		4.00
405015	17-Dec-14		4.00
405015	16-Dec-14		4.00
405015	15-Dec-14		4.00
405015	14-Dec-14		4.00
405015	13-Dec-14	3	3.00
405015	12-Dec-14		3.00

[Anonymous · Accepted solution]

Then try:

If(IsNull(Peek('Nieuw',-1)), [Aantal-oud], if(IsNull([Aantal-oud]),Peek('Nieuw',-1),[Aantal-oud]))as Nieuw

[tomhovens]

Right answer, thx

[tomhovens]

The Peek function works perfectly but....when a new Artikelnr starts it "peek" the amount of the previous Artikelnr

Artikelnr X first entry was at 21-Jul-14. So the peek should stop when a new Artikelnr starts

Artikelnr	Datum	Nieuw
X	22-Jul-14	40.00
X	21-Jul-14	40.00
X	20-Jul-14	15.00
X	19-Jul-14	15.00
X	18-Jul-14	15.00

[Anonymous]

Just modify the if statement:

If(IsNull(Peek('Nieuw',-1)), if(not(IsNull([Aantal-oud])),[Aantal-oud]), if(IsNull([Aantal-oud]),Peek('Nieuw',-1),[Aantal-oud])) as Nieuw