MAX date with respective distinct ID

[Tool_Tip]

Dear All,

We have values as below :

ID   Date

1     01/01/2020

1     01//02/2021

1     01/03/2023

2     XYZ

3     01/02/2024

4     - 

5    01/01/2026

5    01/01/2027

5    -

We need to display ID with max date and if there is no date(NULL) or any text needs to display as it is

ID   MAX_DATE

1   01/03/2023

2   XYZ

3    01/02/2024

4    -

5   01/01/2027

Case 1: If ID with only null date then need to display ID with null date

Case 2: If ID has only text in date then need to display ID with same text as date

case 3: ID ID has multiple date and one null date then need to display max date out of the list.

[TauseefKhan]

Hi @Tool_Tip,

LOAD
ID,
If(IsNum(Date#(Date, 'DD/MM/YYYY')), Date(Date#(Date, 'DD/MM/YYYY')), Date) AS Date
Resident Data;

FinalResult:
LOAD
ID,
MaxString(Date) AS MAX_DATE
Resident Result
Group By ID;

DROP Table Data;
DROP Table Result;

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Tool_Tip]

Hi Tauseef,

Resident Result

DROP Table Result;

?

[TauseefKhan]

Result_Table:
LOAD
ID,
If(IsNum(Date#(Date, 'DD/MM/YYYY')), Date(Date#(Date, 'DD/MM/YYYY')), Date) AS Date
Resident Your_Data_Table; 

FinalResult:
LOAD
ID,
MaxString(Date) AS MAX_DATE
Resident Result
Group By ID;

DROP Table Your_Data_Table;
DROP Table Result_Table;

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***