Max Date within set of dates in another field for each id

[fishing_weights]

Original Table:

Report date	ID_dates	ID
1 Jan 23	3 Dec 22	1
1 Jan 23	4 Dec 22	1
1 Jan 23	5 Nov 22	2
1 Jan 23	6 Nov 22	2
1 Feb 23	4 Dec 22	1
1 Feb 23	5 Dec 22	1
1 Feb 23	6 Nov 22	2
1 Feb 23	8 Nov 22	2

The needed results

Report Date	Max ID_dates	ID
1 Jan 23	4 Dec 22	1
1 Jan 23	6 Nov 22	2
1 Feb 23	5 Dec 22	1
1 Feb 23	8 Nov 22	2

Basically I need the max(ID_dates) for each ID for each reporting month.

I've tried max and group by, order by and then using id <> previous(id) but it all doesn't seems to work.

Note this must be done in my load script and not at the set analysis level.

thanks in advance.

[rubenmarin]

Hi, try loading the original table and do an inner join the keep the rows with max date, like:

OriginalTable:
LOAD...
FROM...

Inner Join (OriginalTable)
LOAD
  [Report date],
  ID,
  Date(Max(ID_dates),'D MMM YY') as ID_dates
Resident OriginalTable
Group By [Report date],ID;

This might work if ID_dates is already a date, if not you can can convert it to date while loading in the OriginalTable using Date#():

Date(Date#(ID_dates,'D MMM YY'),'D MMM YY') as ID_dates