topic Re: How to pick the maximum and minimum date for each row ? in App Development

How to pick the maximum and minimum date for each row ?

Xabinav — Sat, 16 Nov 2024 21:15:52 GMT

Hello everyone,
I need help to find the maximum and minimum date for each row or DealID (in this case).

My source table looks like this

I'm trying to make it...

Thank you very much, Adiel

tresB — Tue, 12 Mar 2019 10:36:24 GMT

You could try with range functions, i.e. - RangeMin() and RangeMax()

shiveshsingh — Tue, 12 Mar 2019 10:50:04 GMT

T:LOAD * INLINE [
DealID, Date1, Date2, Date3
1010, 10/02/2018, 20/03/2018, 04/01/2018
1234, 28/08/2018, 14/01/2018, 22/07/2018
];

D:CrossTable(Date1,Date) load *Resident T;

drop table T;
NoConcatenate
F:
load Date(Date#(Date,'DD/MM/YYY'),'DD/MM/YYY')as Date, Date1,DealID
Resident D;

drop Table D;

Dimension - DealID

Exp - date(max(Date)), date(min(Date))

vitaliichupryna — Tue, 12 Mar 2019 11:12:54 GMT

Hi,

You can do this in following way:

_Data:
Load * Inline [
DeadID, Date1, Date2, Date3
1010, 10/02/2018, 20/03/2018, 04/01/2018,
1234, 28/08/2018, 14/01/2018, 22/07/2018
];

Data:
CrossTable(Date1, Data)
LOAD DeadID,
Date1,
Date2,
Date3
Resident _Data;

DROP Table _Data;

MinMax:
LOAD
DeadID,
Date(Max(Data)) AS MaxDate,
Date(Min(Data)) AS MinDate
Resident Data
Group By DeadID;

DROP Table Data;