Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Check if Previous Previous field is null

Hello,

I am trying to fill previous empty fields with the value of the previous row.

I will explain this with an example. I am using the following table.

KeyPartMonthVoorraad
ExamplePart101-01-201321
ExamplePart101-02-201314
ExamplePart101-03-201312
ExamplePart101-04-201377
ExamplePart101-05-2013-
ExamplePart101-06-201370
ExamplePart101-07-201370
ExamplePart201-01-201333
ExamplePart201-02-201327
ExamplePart201-03-201325
ExamplePart201-04-2013-
ExamplePart201-05-2013-
ExamplePart201-06-201320
ExamplePart201-07-201320

I am using the following code in my script:

if(IsNull([Stock]),Previous([Stock]), [Stock]) as [Stock],

This works (partially), and I get the following result:

KeyPartMonthVoorraad
ExamplePart101-01-201321
ExamplePart101-02-201314
ExamplePart101-03-201312
ExamplePart101-04-201377
ExamplePart101-05-201370
ExamplePart101-06-201370
ExamplePart101-07-201370
ExamplePart201-01-201333
ExamplePart201-02-201327
ExamplePart201-03-201325
ExamplePart201-04-2013-
ExamplePart201-05-201320
ExamplePart201-06-201320
ExamplePart201-07-201320

Only problem is the 01-04-2013 date for ExamplePart2.

I have alot of cases where 2 or more months are empty. How can I change the IF statement so it fills, lets say, even 6 months back?

I was trying the following:

if(IsNull([Stock]),Previous([Stock]),

          if(isNull(Previous([Stock])), Previous(Previous([Stock])), [Stock])) as [Stock],

But this doesnt seem to work. Any ideas?

6 Replies
MVP
MVP

Re: Check if Previous Previous field is null

Try This:

if(IsNull([Stock]),Peek(New_Stock), [Stock]) as New_Stock

Not applicable

Re: Check if Previous Previous field is null

if([Stock]=' ',Previous([Stock]), [Stock]) as [Stock],

Not applicable

Re: Check if Previous Previous field is null

try this

temp:

LOAD * INLINE [

    KeyPart, Month, Voorraad

    ExamplePart1, 01-01-2013, 21

    ExamplePart1, 01-02-2013, 14

    ExamplePart1, 01-03-2013, 12

    ExamplePart1, 01-04-2013, 77

    ExamplePart1, 01-05-2013, -

    ExamplePart1, 01-06-2013, 70

    ExamplePart1, 01-07-2013, 70

    ExamplePart2, 01-01-2013, 33

    ExamplePart2, 01-02-2013, 27

    ExamplePart2, 01-03-2013, 25

    ExamplePart2, 01-04-2013, -

    ExamplePart2, 01-05-2013, -

    ExamplePart2, 01-06-2013, 20

    ExamplePart2, 01-07-2013, 20

];

LOAD *,

if(Voorraad='-',Previous(Voorraad),Voorraad) as Voorraad_new

Resident temp;

then output like this

KeyPartMonthVoorraad_new
ExamplePart101-01-201321
ExamplePart101-02-201314
ExamplePart101-03-201312
ExamplePart101-04-201377
ExamplePart101-05-201377
ExamplePart101-06-201370
ExamplePart101-07-201370
ExamplePart201-01-201333
ExamplePart201-02-201327
ExamplePart201-03-201325
ExamplePart201-04-201325
ExamplePart201-05-2013-
ExamplePart201-06-201320
ExamplePart201-07-201320

see attachement

florentina_doga
Contributor III

Re: Check if Previous Previous field is null

try:

if(Voorraad='',Peek(Voorraad_new),Voorraad) as Voorraad_new

flipside
Valued Contributor II

Re: Check if Previous Previous field is null

If your data is like this at source, then you may be able to use the Transformation step to fill in missing values, otherwise you may also be able to export the table to a qvd and read it back in with transformation step ...

Store A into A.qvd (qvd);

Drop Table A;

A:

LOAD @1 as KeyPart,

     @2 as Month,

     @3 as Stock

FROM

[A.qvd]

(qvd, filters(

Replace(3, top, StrCnd(equal, '-'))

));

flipside

Not applicable

Re: Check if Previous Previous field is null

in expression side--

try this-

in straight--

dimension1-    KeyPart

dimension2-    Month

expression-   if(Voorraad='-',Below(Voorraad),Voorraad)

then outpul like this

KeyPartMonthif(Voorraad='-',Below(Voorraad),Voorraad)
-
ExamplePart101-01-201321
ExamplePart101-02-201314
ExamplePart101-03-201312
ExamplePart101-04-201377
ExamplePart101-05-201370
ExamplePart101-06-201370
ExamplePart101-07-201370
ExamplePart201-01-201333
ExamplePart201-02-201327
ExamplePart201-03-201325
ExamplePart201-04-2013-
ExamplePart201-05-201320
ExamplePart201-06-201320
ExamplePart201-07-201320
Community Browser