topic Load only rows with max date by ID in QlikView

Load only rows with max date by ID

zakpullen — Fri, 21 Aug 2020 13:45:52 GMT

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

Re: Load only rows with max date by ID

Kushal_Chawda — Fri, 21 Aug 2020 14:16:40 GMT

Data:

load ID,

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

FROM Table

group by ID;

Re: Load only rows with max date by ID

JGMDataAnalysis — Fri, 21 Aug 2020 14:31:36 GMT

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

Re: Load only rows with max date by ID

zakpullen — Thu, 27 Aug 2020 15:24:41 GMT

Thanks. I used a variation of this

Re: Load only rows with max date by ID

zakpullen — Thu, 27 Aug 2020 15:26:09 GMT

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.