topic Re: Check if Previous Previous field is null in QlikView

Check if Previous Previous field is null

Mon, 08 Jul 2013 10:11:02 GMT

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?

Re: Check if Previous Previous field is null

tresB — Mon, 08 Jul 2013 10:20:58 GMT

Try This:

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

Re: Check if Previous Previous field is null

Mon, 08 Jul 2013 10:30:35 GMT

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

Re: Check if Previous Previous field is null

Mon, 08 Jul 2013 10:36:29 GMT

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

Re: Check if Previous Previous field is null

florentina_doga — Mon, 08 Jul 2013 10:45:50 GMT

try:

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

Re: Check if Previous Previous field is null

flipside — Mon, 08 Jul 2013 11:08:17 GMT

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;

LOAD @1 as KeyPart,

@2 as Month,

@3 as Stock

FROM

[A.qvd]

(qvd, filters(

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

));

flipside

Re: Check if Previous Previous field is null

Mon, 08 Jul 2013 11:10:15 GMT

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