Peek Last record

[#1]

Hi All,

In my DB there is audit trial table.  Once I filter in OrderNo it will shown mutiple row.

My need is once I filter OrderNo the row should shown 1 row which is with latest EditDate (row highlighted in pink color).

I'm tried to use last value for RecordId and peek function for EditDate but failed

OrderNo	RecordId	DepartDateTime	EditDate
OUB359893	3236523	10-10-2015 14:00:00	10/13/2015 14:21
OUB359893	3234090	10-10-2015 14:05:00	10/12/2015 17:19
OUB359893	3236510	13-10-2015 14:00:00	10/13/2015 14:19

I attached together with qvw.

Please assist me.  Thank in advance.

[Anonymous]

I have made some changes at script level,

just run it from your data source, you would be getting last ID at UI..

[Anonymous]

in a normal table you cannnot use expressions, just define existing fields

if you use a straight table instead

you can define dimensions (OrderNo,RecordId) and expressions where you can define =aggr(max(DepartDateTime),OrderNo) and aggr(max(EditDate),OrderNo) and it works

by the way, last edit date is 10/13 and not your highlighted row

[maxgro · Accepted solution]

add a flag in your script to identify the max

Left Join (TEMP7)

load

  OrderNo,

1 as FlagMax,

  Date(max(EditDate)) as EditDate

Resident TEMP7

Group By OrderNo;

[#5]

Here is a solution that works for me

MaxDate:

  Load

  MAX(date(UPDATE_DATE,'DD/MM/YYYY')) as maximum

  From ..DATA

  LET vMAX = PEEK('maximum', 0, 'MaxDate');

LATESTRECORD::

  LOAD

ORDER_INFO,

     UPDATE_DATE

    FROM

DATA

  WHERE DayName("UPDATE_DATE ")=DayName('$(vMAX)');

[Kushal_Chawda]

Create the straight table with Expression

=aggr(max(EditDate), OrderNo)

See the attached

[#7]

This only peek for date....in the column is date and time

[#8]

Hairul

Change

MAX(date(UPDATE_DATE,'DD/MM/YYYY')) as maximum

to

MAX(date(UPDATE_DATE,'DD/MM/YYYY hh:mm:ss')) as maximum

Regards

Declan