QlikView: Select Last Row

[marina24]

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;

[sunny_talwar · Accepted solution]

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;

[marina24]

Thank you, dear Sunny! ☀️

[marina24]

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

In this case, this method does not work unfortunately

[sunny_talwar]

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

[marina24]

@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!!!

[sunny_talwar]

Nice