Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
MEDHA07
Contributor III
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)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try this

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

View solution in original post

2 Replies
West69
Contributor II
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.

Vegar
MVP
MVP

Try this

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