Finding min date and max date of each status

[Jack7]

Hi all,

I have a table below and I am trying to find the min and max date for every event per employee

Employee ID	From Date	To Date	Status
1	26/01/2021	1/04/2021	USA
1	1/05/2021	9/10/2021	USA
1	10/10/2021	12/11/2021	New Zealand
1	13/11/2021	30/11/2021	Australia
1	1/12/2021	2/01/2022	Australia
1	3/01/2022	6/03/2022	New Zealand
1	7/03/2022	12/12/2022	USA
2	15/04/2020	20/05/2020	Australia
2	21/05/2020	1/06/2020	Australia
2	10/06/2020	14/08/2020	New Zealand
2	15/08/2020	7/12/2020	New Zealand
2	8/12/2020	1/01/2021	Australia
2	2/01/2021	5/03/2021	Australia

My desired output would look like this:

Employee	From Date	To Date	Status
1	26/01/2021	9/10/2021	USA
1	10/10/2021	12/11/2021	New Zealand
1	13/11/2021	2/01/2022	Australia
1	3/01/2022	6/03/2022	New Zealand
1	7/03/2022	12/12/2022	USA
2	15/04/2020	1/06/2020	Australia
2	10/06/2020	7/12/2020	New Zealand
2	8/12/2020	5/03/2021	Australia

Unfortunately I have tried the group by method and it did not work in this scenario. 

Thankyou!

[MayilVahanan · Accepted solution]

Hi

Try like below

Temp:
LOAD *, if([Employee ID] = Previous([Employee ID]), if(Status <> Previous(Status), Alt(Peek('Trip')+1,1) , Peek('Trip')), 1) as Trip INLINE [
Employee ID, From Date, To Date, Status
1, 26/01/2021, 1/04/2021, USA
1, 1/05/2021, 9/10/2021, USA
1, 10/10/2021, 12/11/2021, New Zealand
1, 13/11/2021, 30/11/2021, Australia
1, 1/12/2021, 2/01/2022, Australia
1, 3/01/2022, 6/03/2022, New Zealand
1, 7/03/2022, 12/12/2022, USA
2, 15/04/2020, 20/05/2020, Australia
2, 21/05/2020, 1/06/2020, Australia
2, 10/06/2020, 14/08/2020, New Zealand
2, 15/08/2020, 7/12/2020, New Zealand
2, 8/12/2020, 1/01/2021, Australia
2, 2/01/2021, 5/03/2021, Australia
];

NoConcatenate
EmpFinal:
Load [Employee ID], Trip, Status, Min([From Date]) as [From Date], Max([To Date]) as [To Date] Resident Temp
Group by [Employee ID], Trip, Status;

DROP Table Temp;

Drop Field Trip;

[Jack7]

Thankyou, appreciate it!