Check if Previous Previous field is null

[#1]

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.

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

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:

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	70
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	20
ExamplePart2	01-06-2013	20
ExamplePart2	01-07-2013	20

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?

[tresesco]

Try This:

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

[#3]

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

[#4]

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

KeyPart	Month	Voorraad_new
ExamplePart1	01-01-2013	21
ExamplePart1	01-02-2013	14
ExamplePart1	01-03-2013	12
ExamplePart1	01-04-2013	77
ExamplePart1	01-05-2013	77
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	25
ExamplePart2	01-05-2013	-
ExamplePart2	01-06-2013	20
ExamplePart2	01-07-2013	20

see attachement

[florentina_doga]

try:

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

[flipside]

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

[#7]

in expression side--

try this-

in straight--

dimension1-    KeyPart

dimension2-    Month

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

then outpul like this

KeyPart	Month	if(Voorraad='-',Below(Voorraad),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	70
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	20
ExamplePart2	01-06-2013	20
ExamplePart2	01-07-2013	20