topic selected only last date status in QlikView

selected only last date status

Wed, 25 Nov 2020 16:16:04 GMT

hello,

i'm begginer and I need a little help.

I got columns:

name, surname, id, status, status_date.

And i need show only data with the last status,

ex:

Adam | Nowak | 5 | close | 2015-02-02 <--- need only this row

Adam | Nowak | 5 | open | 2015-02-01

ex2:

Adam | Nowak | 54 | open | 2013-12-05

Adam | Nowak | 54 | stop | 2013-01-02

Adam | Nowak | 54 | close | 2013-12-19 <--- need only this row

Adam | Nowak | 54 | stop | 2013-07-09

Adam | Nowak | 54 | open | 2013-02-01

How to use the FirstSortedValue? or another way?

Thank You!

Re: selected only last date status

sunny_talwar — Wed, 20 Apr 2016 15:28:14 GMT

Not sure if this is needed in the script or UI, but try this:

Straight Table

Dimension

Name,

Surname

Expressions

Date(Max(status_date))

FirstSortedValue(status, -status_date)

Re: selected only last date status

johnw — Wed, 20 Apr 2016 19:17:54 GMT

I sometimes do something like this:

Something:
LOAD
ID
,Timestamp
,BunchOfData
from wherever
;
INNER JOIN (Something)
LOAD
ID
,max(Timestamp) as Timestamp
RESIDENT Something
;

Depending upon what "from wherever" means, it might be faster to reload from wherever. This is clearly not responsive to selections, and removes all the other records. If you just want to flag the records rather than removing, then do a left join, and add a flag value in the left join, true() as Flag, for instance. Then you could use set analysis on the flag value to display. Or take another pass and make a Last Status value.

[Last Something]:
LOAD
ID
,Status as [Last Status]
RESIDENT Something
WHERE Flag
;

Still not responsive to selections, but I wouldn't think you'd want something like this to be responsive to selections.

I can't help thinking there's a more efficient way to write the script, though.