topic Re: QlikView: Select Last Row in QlikView

QlikView: Select Last Row

marina24 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I want to define the last row and fill the "Result" column with the "STATUS" value (screenshot #1). How I can do it?

[Table]:
LOAD GUID, STATUS, Date(DATE_EDIT, 'DD.MM.YYYY') as DATE_EDIT
FROM
test300920.xlsx
(ooxml, embedded labels);

[Table1]:
NoConcatenate
LOAD GUID, STATUS, DATE_EDIT, AutoNumber(RowNo(),GUID)
Resident Table
Order by GUID, DATE_EDIT asc;

DROP TABLE Table;

Re: QlikView: Select Last Row

sunny_talwar — Wed, 30 Sep 2020 13:40:43 GMT

Try this

[Table]: LOAD GUID, STATUS, Date(DATE_EDIT, 'DD.MM.YYYY') as DATE_EDIT FROM test300920.xlsx (ooxml, embedded labels); [Table1]: NoConcatenate LOAD GUID, STATUS, DATE_EDIT, AutoNumber(RowNo(),GUID) Resident Table Order by GUID, DATE_EDIT asc; Left Join (Table1) LOAD GUID, Max(DATE_EDIT) as DATE_EDIT, FirstSortedValue(STATUS, -DATE_EDIT) as RESULT Resident Table1 Group By GUID; DROP TABLE Table;

Re: QlikView: Select Last Row

marina24 — Wed, 30 Sep 2020 13:46:57 GMT

Thank you, dear Sunny! ☀️

Re: QlikView: Select Last Row

marina24 — Wed, 30 Sep 2020 14:15:25 GMT

@sunny_talwar , There are may be records with the same dates. Like this:

In this case, this method does not work unfortunately

Re: QlikView: Select Last Row

sunny_talwar — Wed, 30 Sep 2020 14:19:39 GMT

@marina24 which of the two status should be displayed here? for GUID = 3?

Re: QlikView: Select Last Row

marina24 — Wed, 30 Sep 2020 14:55:25 GMT

@sunny_talwar Sorry, I didn't take that into account. But your question gave me an idea of what changes need to be made. Thank you!!!

Re: QlikView: Select Last Row

sunny_talwar — Wed, 30 Sep 2020 14:57:47 GMT

Nice