Display Date when Value become negative

[ch_riadh]

Hi, I have this table,

ACCOUNT	DATE	AMOUNT
101	1/2/2020	-500
101	1/17/2020	1000
101	2/25/2020	-200
101	2/29/2020	300
102	2/9/2020	600
102	2/11/2020	700
102	3/21/2020	-800
102	4/26/2020	-900

I want to display from which last date the value change to negative for each ACCOUNT.

ACCOUNT	DATE	AMOUNT
101	2/25/2020	-200
102	3/21/2020	-800

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD *, If(K1>1 And Peek(AMOUNT)>0 And AMOUNT<0,'Y') As Flag;
LOAD *, AutoNumber(RecNo(),ACCOUNT) As K1;
LOAD * INLINE [
    ACCOUNT, DATE, AMOUNT
    101, 1/2/2020, -500
    101, 1/17/2020, 1000
    101, 2/25/2020, -200
    101, 2/29/2020, 300
    102, 2/9/2020, 600
    102, 2/11/2020, 700
    102, 3/21/2020, -800
    102, 4/26/2020, -900
];

Drop Field K1;

[Saravanan_Desingh]

Output.

[NitinK7 · Accepted solution]

Hi

try below

abc:
load * inline [
ACCOUNT, DATE, AMOUNT
101, 1/2/2020, -500
101, 1/17/2020, 1000
101, 2/25/2020, -200
101, 2/29/2020, 300
102, 2/9/2020, 600
102, 2/11/2020, 700
102, 3/21/2020, -800
102, 4/26/2020, -900
];

NoConcatenate
pqr:
load ACCOUNT as Account,
DATE as Date,
AMOUNT as Amount
where AMOUNT<0;

load
ACCOUNT,
DATE,
if(Previous(AMOUNT)>0,AMOUNT)as AMOUNT
Resident abc;

[ch_riadh]

Thank You very much

[ch_riadh]

It Works 
thank you bor

[Kushal_Chawda]

I don't think solution using peek and previous will work here because let's say if you found negative value on 2/11/2020 for ID 102. For dynamic solution refer to below thread

https://community.qlik.com/t5/Qlik-Sense-App-Development/Display-Date-when-Value-become-negative/m-p...