topic Re: Display Date when Value become negative in QlikView

Display Date when Value become negative

ch_riadh — Sun, 16 Aug 2020 08:56:10 GMT

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

Re: Display Date when Value become negative

Saravanan_Desingh — Sun, 16 Aug 2020 15:52:36 GMT

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;

Re: Display Date when Value become negative

Saravanan_Desingh — Sun, 16 Aug 2020 15:53:40 GMT

Output.

Re: Display Date when Value become negative

NitinK7 — Sun, 16 Aug 2020 16:05:31 GMT

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;

Re: Display Date when Value become negative

ch_riadh — Sun, 16 Aug 2020 17:13:30 GMT

Thank You very much

Re: Display Date when Value become negative

ch_riadh — Sun, 16 Aug 2020 17:14:01 GMT

It Works
thank you bor

Re: Display Date when Value become negative

Kushal_Chawda — Sun, 16 Aug 2020 21:41:49 GMT

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/1736176#M56262