Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ch_riadh
Partner - Creator II
Partner - Creator II

Display Date when Value become negative

Hi, I have this table,

ACCOUNTDATEAMOUNT
1011/2/2020-500
1011/17/20201000
1012/25/2020-200
1012/29/2020300
1022/9/2020600
1022/11/2020700
1023/21/2020-800
1024/26/2020-900

 

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

ACCOUNTDATEAMOUNT
1012/25/2020-200
1023/21/2020-800
2 Solutions

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

NitinK7
Specialist
Specialist

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;

Capture.JPG

View solution in original post

6 Replies
Saravanan_Desingh

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.

commQV66.PNG

NitinK7
Specialist
Specialist

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;

Capture.JPG

ch_riadh
Partner - Creator II
Partner - Creator II
Author

Thank You very much

ch_riadh
Partner - Creator II
Partner - Creator II
Author

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...