Max Date for table

[AhmDD]

Hi All!

Need your help!

I have searched similar cases but seems that this is some another case.

So I have a table with several fields: "item number", "POS code", "installation date" and some other but we need these three main.

The main field is "item number" - and there can be several  rows for this field because of different "POS code" and different "installation date".

What i need - is to leave in the table only one row for each "item number" with "POS code" which has maximum value of "installation date" (or "POS code" with the last date of installation ).

For example currently I have this table:

item number	POS code	installation date
1	key655464	27.09.2022
1	key655464	28.09.2022
1	-	26.09.2022
2	key231	11.09.2022
2	key321	12.09.2022
2	key456	13.09.2022
3	key951	25.09.2022
3	key368	27.09.2022
3	-	27.09.2021
3	-	01.09.2021

the result should be:

item number	POS code	installation date
1	key655464	28.09.2022
2	key456	13.09.2022
3	key368	27.09.2022

best regards

[maxgro · Accepted solution]

You can  read (resident load) the table  sorted by [item number] and [installation date] desc; filter the records and keep only the ones where [item number] changes (WHERE [item number] <> Previous([item number])).

Tmp:
LOAD [item number], [POS code], [installation date]
FROM
[https://community.qlik.com/t5/App-Development/Max-Date-for-table/td-p/1987560]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Data2:
NOCONCATENATE LOAD *
RESIDENT Tmp
WHERE [item number] <> Previous([item number])
ORDER BY [item number], [installation date] desc;

DROP TABLE Tmp;

[AhmDD]

Hi Maxgro! This is great!

To be clear for others – I have used my main load table (without TMP) and then Data2:

Data2:

NOCONCATENATE LOAD *

RESIDENT MainTable

WHERE [item number] <> Previous([item number])

ORDER BY [item number], [installation date] desc;

DROP TABLE MainTable;

Many many thanks!!!

Best regards