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;

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

14 Replies
Gysbert_Wassenaar

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

Tom,

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

Marc.

tomhovens
Contributor II
Contributor II
Author

Where should I paste it in the script?

sunny_talwar

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

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


talk is cheap, supply exceeds demand
tomhovens
Contributor II
Contributor II
Author

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

Tom,

Don't use square brackets in Peek function:

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

tomhovens
Contributor II
Contributor II
Author

Marc,

used the next code:

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

Still the same result

Anonymous
Not applicable

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.