topic Re: Max Date for table in App Development

Max Date for table

AhmDD — Fri, 30 Sep 2022 12:03:50 GMT

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

Re: Max Date for table

maxgro — Fri, 30 Sep 2022 13:05:06 GMT

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;

Re: Max Date for table

AhmDD — Sat, 01 Oct 2022 15:35:24 GMT

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