Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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