Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomhovens
Contributor II
Contributor II

Peek & IsNull

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;

14 Replies
tomhovens
Contributor II
Contributor II
Author

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
Not applicable

Then try:

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

tomhovens
Contributor II
Contributor II
Author

Right answer, thx

tomhovens
Contributor II
Contributor II
Author

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
Not applicable

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