Trying to get Min/Max Dates based of another field

[DJHodgson]

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

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

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

Status	From Date	To Date	Days
N	30/05/2019	29/01/2020	241
Y	30/01/2020	15/06/2020	136
N	16/06/2020	27/10/2020	133

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

Status	From Date	To Date	Days
N	30/05/2019	27/10/2020	516
Y	30/01/2020	15/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

[Taoufiq_Zarra · Accepted solution]

@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:

in qlikview we can hide some column then you can get

[DJHodgson]

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