Selecting last entry of a groups within a pivot table

[#1]

Hi I have a pivot table showing employees and their clocked in/out status along with the time that they clocked in / out

I only want to show the last transaction for each employee rather than all of the clockings for that day.

I have calculated a rank value to try to help but can't seem to get beyond this.

Simply I have got a table with Employee, Time, Rank

e.g.

Fred, 09:00, In, 1

Fred 09:55,Out 2

Jo, 08:55,In,1

Bert, 08:40,In,1

Bert, 12:40,Out,2

Bert, 14:40,In,3

I want to only show the entries in bold

Thanks in advance for any help

[Gysbert_Wassenaar]

You can use the firstsortedvalue for this. You can use Type and Name as dimensions and as expressions:

Time: firstsortedvalue(Time, -Rank)

Rank: max(Rank)

In/Out: firstsortedvalue(In_Out_Field, -Rank)

see attached qvw.

[#3]

Attached the QV file for perusal

[#4]

Thanks for that, I've added an expression

firstsortedvalue(EMPLNO, -CLCKTM) but it is  returning a blank

Also tried

firstsortedvalue(EMPLNO, -Rank) with the same result

[#5]

Mea Culpa, I misunderstood the use of the Dimension for sorting.

All working now

Thnks Gysbert

[#6]

see attachement

test:

LOAD * INLINE [

    employee, time, status, rank

    Fred, 09:00, In, 1

    Fred, 09:55, Out, 2

    Jo, 08:55, In, 1

    Bert, 08:40, In, 1

    Bert, 12:40, Out, 2

    Bert, 14:40, In, 3

];

LOAD   

      employee as employeenew,

      max(time(time,'hh:mm')) as maxtime ,

      FirstSortedValue(status,-time(time,'hh:mm')) as statusnew,

      FirstSortedValue(rank,-time(time,'hh:mm')) as ranknew

      Resident test Group by employee;

DROP Table test;  

then take a pivote/straight table--

dimension= employeenew

expression1=maxtime

expression2=statusnew

expression3=ranknew

then in sort tab-->select load order

then output like this

employeenew	maxtime	statusnew	ranknew
	-	-	-
Fred	09:55	Out	2
Jo	08:55	In	1
Bert	14:40	In	3

[er_mohit]

see the attached file