topic Peek & IsNull in QlikView

Peek & IsNull

tomhovens — Mon, 26 Jan 2015 11:10:52 GMT

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;

Re: Peek & IsNull

Gysbert_Wassenaar — Mon, 26 Jan 2015 11:17:44 GMT

You specifically convert nulls into empty strings:

NULLASVALUE *;
SET Nullvalue ='';

So IsNull will never return true. You can try len(trim(...))>0 or the IsNum function if Aantal-oud should always contain numbers.

Re: Peek & IsNull

Anonymous — Mon, 26 Jan 2015 11:18:53 GMT

Tom,

Use Peek function like this: Peek('Artikelnr').

Marc.

Re: Peek & IsNull

tomhovens — Mon, 26 Jan 2015 12:03:30 GMT

Where should I paste it in the script?

Re: Peek & IsNull

sunny_talwar — Mon, 26 Jan 2015 13:18:03 GMT

marcarreras is right, Peek function always take the field name within single quotes. Try running the script after making the following change to your Voorraadverslag table:

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

Best,

Re: Peek & IsNull

Gysbert_Wassenaar — Mon, 26 Jan 2015 14:44:32 GMT

Paste what? I'm pretty sure I didn't say anything about pasting.

Re: Peek & IsNull

tomhovens — Tue, 27 Jan 2015 07:44:34 GMT

I used the next code

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

When I use the single quotes within the peek function I only get the known result, when I don't the next line works

Artikelnr	Datum	Aantal-oud	Nieuw
405015	25-Dec-14
405015	24-Dec-14
405015	23-Dec-14
405015	22-Dec-14
405015	21-Dec-14
405015	20-Dec-14
405015	19-Dec-14	4	4.00
405015	18-Dec-14		4.00
405015	17-Dec-14
405015	16-Dec-14
405015	15-Dec-14
405015	14-Dec-14

Re: Peek & IsNull

Anonymous — Tue, 27 Jan 2015 07:47:09 GMT

Tom,

Don't use square brackets in Peek function:

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

Re: Peek & IsNull

tomhovens — Tue, 27 Jan 2015 08:03:56 GMT

Marc,

used the next code:

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

Still the same result

Re: Peek & IsNull

Anonymous — Tue, 27 Jan 2015 08:16:13 GMT

Tom, at first you asked about the Isnull function. Now, I am not pretty sure about your expected result. Please elaborate your request if you still need help with this.

Marc.

Re: Peek & IsNull

tomhovens — Tue, 27 Jan 2015 09:07:15 GMT

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

Re: Peek & IsNull

Anonymous — Tue, 27 Jan 2015 09:29:59 GMT

Then try:

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

Re: Peek & IsNull

tomhovens — Tue, 27 Jan 2015 09:39:21 GMT

Right answer, thx

Re: Peek & IsNull

tomhovens — Wed, 28 Jan 2015 08:00:38 GMT

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

Re: Peek & IsNull

Anonymous — Wed, 28 Jan 2015 08:19:44 GMT

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