Load only rows with max date by ID

[zakpullen]

Hi,

I have data where a small number of rows can be duplicates except for 1 date field. In these instances, I only want to load the row with the max date. There are other columns in the data, but essentially I'm after the result below.

Many thanks

[Kushal_Chawda · Accepted solution]

Data:

load ID,

         date(max([ Expiry Date])) as [ Expiry Date]

FROM Table

group by ID;

[JGMDataAnalysis]

DesiredTable:
NOCONCATENATE
LOAD *
RESIDENT SourceTable
WHERE ID <> Previous(ID)
ORDER BY ID, [Expiry Date] DESC
;

DROP TABLE SourceTable;

[zakpullen]

Thanks. I used a variation of this

[zakpullen]

Thanks. I went ahead with the first reply, but I did play around with this and it worked. It's a new technique to me that I may use in the future.