Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.