Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

tomhovens
Not applicable

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;

Tags (1)
1 Solution

Accepted Solutions
marcarreras
Not applicable

Re: Peek & IsNull

Then try:

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

14 Replies
Gysbert_Wassenaar
Not applicable

Re: Peek & IsNull

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.


talk is cheap, supply exceeds demand
marcarreras
Not applicable

Re: Peek & IsNull

Tom,

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

Marc.

tomhovens
Not applicable

Re: Peek & IsNull

Where should I paste it in the script?

sunny_talwar
Not applicable

Re: Peek & IsNull

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,

S

Gysbert_Wassenaar
Not applicable

Re: Peek & IsNull

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


talk is cheap, supply exceeds demand
tomhovens
Not applicable

Re: Peek & IsNull

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

Re: Peek & IsNull

Tom,

Don't use square brackets in Peek function:

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

tomhovens
Not applicable

Re: Peek & IsNull

Marc,

used the next code:

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

Still the same result

marcarreras
Not applicable

Re: Peek & IsNull

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.