Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
marcarreras
Specialist
Specialist

Then try:

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

View solution in original post

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
marcarreras
Specialist
Specialist

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