Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Creator
Creator

Load only rows with max date by ID

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.

IMG.JPG

Many thanks

1 Solution

Accepted Solutions
Kushal_Chawda

Data:

load ID,

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

FROM Table

group by ID;

 

View solution in original post

4 Replies
Kushal_Chawda

Data:

load ID,

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

FROM Table

group by ID;

 

JGMDataAnalysis
Creator III
Creator III

 

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

DROP TABLE SourceTable;

 

zakpullen
Creator
Creator
Author

Thanks. I used a variation of this

zakpullen
Creator
Creator
Author

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.