Discussion Board for collaboration related to Creating Analytics for QlikView.
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.
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:
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],
if(isNull(Previous([Stock])), Previous(Previous([Stock])), [Stock])) as [Stock],
But this doesnt seem to work. Any ideas?
if(IsNull([Stock]),Peek(New_Stock), [Stock]) as New_Stock
if([Stock]=' ',Previous([Stock]), [Stock]) as [Stock],
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
if(Voorraad='-',Previous(Voorraad),Voorraad) as Voorraad_new
then output like this
if(Voorraad='',Peek(Voorraad_new),Voorraad) as Voorraad_new
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
Replace(3, top, StrCnd(equal, '-'))
in expression side--
then outpul like this