how to get previous row value when next row value is zero or null

[MEDHA07]

Hi All,

I need to get the previous row value when next row value is zero even it has different numbers.just needed previous row value in qlik straight table.

I have numbers and it has locations.few of them having single number with one location and some of them have same number with different locations .

1.for  number 45678 has two rows with different location one has zero and other one has value.but i need to show the previous value if it has zero or null.

suppose,same  number 45678 location 1 has zero value and location 2 has number(10000) this case it should show the previous row value it may be the different number and location.it should not show the location 2 value(10000) for location 1.

2.if unique row with one location if it has zero value then it should show the previous row value.

No	LocationNo	Amount	Expected results
45678	1	10000	10000
45678	2	0	10000
6789	1	0	10000
4523	3	6788	6788
2344	4	19388	19388
36347	1	0	19388
789	5	2345	2345

[West69]

I would normally,do something like below

IF(RowNo() =1 , Fragomen Connect Client Portal

     IF(ISNULL(Value)=-1,0

          IF(ISNULL(Value)=-1, Previous(Value))

          )

     ) AS Value,

But that will only work for one step back, if you could have more than one value blank then you need to look at the peek and longer nested if.

[Vegar · Accepted solution]

Try this

(fabs(Amount)>0, Amount, Peek([Calc Amount]) as [Calc Amount]