Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ch_riadh
Partner
Partner

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
Partner
Author

Thank You very much

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