topic Re: Trying to get Min/Max Dates based of another field in App Development

Trying to get Min/Max Dates based of another field

DJHodgson — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: Trying to get Min/Max Dates based of another field

Taoufiq_Zarra — Wed, 28 Oct 2020 07:48:22 GMT

@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

Re: Trying to get Min/Max Dates based of another field

DJHodgson — Wed, 04 Nov 2020 11:01:59 GMT

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