Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DJHodgson
Contributor
Contributor

Trying to get Min/Max Dates based of another field

Hi I'm wondering if someone could help me please, I am trying to get a min/max date based of another fields so in the below table:-

StatusFrom DateTo DateDays
N30/05/201931/05/20191
N01/06/201903/06/20192
N04/06/201904/06/20190
N05/06/201929/01/2020238
Y30/01/202024/03/202054
Y25/03/202015/06/202082
Y25/03/202025/03/20200
N16/06/202027/10/2020133

 

You can see a list of dates a status has been in N or Y along with the number of days that status has lasted.

 

I need to be able to summarise this to the following:-

StatusFrom DateTo DateDays
N30/05/201929/01/2020241
Y30/01/202015/06/2020136
N16/06/202027/10/2020133

 

The issue I am facing is that as soon as i try to Min/Max the dates its giving me the following:-

StatusFrom DateTo DateDays
N30/05/201927/10/2020516
Y30/01/202015/06/2020

136

 

This is incorrect at the N result needs to have 2 entries either side of the Y and I am coming up with a blank as to how to do this.

 

Any suggestions/solutions would be very gladly appreciated.

 

Many thanks

Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@DJHodgson  you can always do this

in Script:

Input:

LOAD * INLINE [
    Status, From Date, To Date, Days
    N, 30/05/2019, 31/05/2019, 1
    N, 01/06/2019, 03/06/2019, 2
    N, 04/06/2019, 04/06/2019, 0
    N, 05/06/2019, 29/01/2020, 238
    Y, 30/01/2020, 24/03/2020, 54
    Y, 25/03/2020, 15/06/2020, 82
    Y, 25/03/2020, 25/03/2020, 0
    N, 16/06/2020, 27/10/2020, 133
];

final:
noconcatenate

load *,if(rowno()=1,1,if(peek(Status)=Status,peek(Row),peek(Row)+1)) as Row;
load * resident Input order by [From Date];

drop table Input;

 

and in chart

dimension: Row ,Status

Measure
=Date(min([From Date]))

and

=Date(Max([To Date]))

 

output:

Capture.PNG

in qlikview we can hide some column then you can get

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra
Master II
Master II

@DJHodgson  you can always do this

in Script:

Input:

LOAD * INLINE [
    Status, From Date, To Date, Days
    N, 30/05/2019, 31/05/2019, 1
    N, 01/06/2019, 03/06/2019, 2
    N, 04/06/2019, 04/06/2019, 0
    N, 05/06/2019, 29/01/2020, 238
    Y, 30/01/2020, 24/03/2020, 54
    Y, 25/03/2020, 15/06/2020, 82
    Y, 25/03/2020, 25/03/2020, 0
    N, 16/06/2020, 27/10/2020, 133
];

final:
noconcatenate

load *,if(rowno()=1,1,if(peek(Status)=Status,peek(Row),peek(Row)+1)) as Row;
load * resident Input order by [From Date];

drop table Input;

 

and in chart

dimension: Row ,Status

Measure
=Date(min([From Date]))

and

=Date(Max([To Date]))

 

output:

Capture.PNG

in qlikview we can hide some column then you can get

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

DJHodgson
Contributor
Contributor
Author

sorry for the delay yes this worked perfectly for me thank you