cancel
Showing results for
Did you mean:
Contributor III

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

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
Labels (1)
• ### Application Development

1 Solution

Accepted Solutions
MVP

Try this

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

2 Replies
Contributor II

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.

MVP

Try this

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

Community Browser