How to load only the most recent row per group from a QVD

[ananth]

I have a QVD with multiple rows per ID and I want to load only the
most recent row per ID based on a date column.

Sample data:

ID | Date | Value
----|---------------------------|------
A | 2026-04-07 23:35:50.0000000 | 100
A | 2026-03-01 08:00:00.0000000 | 200
A | 2026-01-15 12:00:00.0000000 | 300
B | 2026-04-05 10:00:00.0000000 | 400
B | 2026-02-20 09:00:00.0000000 | 500

Expected result — one row per ID, the most recent:

ID | Date | Value
----|-----------------------------|------
A | 2026-04-07 23:35:50.0000000 | 100
B | 2026-04-05 10:00:00.0000000 | 400

What is the correct approach to achieve this in the load script?

[RafaMartins]

You could say something like:

LOAD
ID,
Date,
Value
FROM Your_Table
WHERE NOT Exists(ID)
ORDER BY ID, Date DESC;

"This way, Qlik will sort all rows by the most recent IDs using ORDER BY ID, Date DESC. Since we are using WHERE NOT Exists(ID), any duplicate rows encountered later in the script will be ignored during the load.

Please note that this is only valid if your data model fits the exact scenario you described (only one row per ID). If you need to bring in multiple rows for the same ID, this approach will not work for you."

[vighnesh_gawad]

Hi @ananth ,

 Try the below code. it will return  most recent record per ID based on Date field. You can replace inline sample data with your data.

Data:
LOAD * INLINE [
ID, Date, Value
A, 2026-04-07 23:35:50, 100
A, 2026-03-01 08:00:00, 200
A, 2026-01-15 12:00:00, 300
B, 2026-04-05 10:00:00, 400
B, 2026-02-20 09:00:00, 500
];

LatestData:
NoConcatenate
LOAD
    ID,
    FirstSortedValue(Date, -Date) as Date,
    FirstSortedValue(Value, -Date) as Value
RESIDENT Data
GROUP BY ID;

DROP TABLE Data;