Peek & IsNull

[tomhovens]

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;

[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.

[Anonymous]

Tom,

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

Marc.

[tomhovens]

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.

[tomhovens]

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]

Tom,

Don't use square brackets in Peek function:

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

[tomhovens]

Marc,

used the next code:

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

Still the same result

[Anonymous]

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.