topic Re: Peek Last record in QlikView

Peek Last record

Fri, 13 Nov 2015 09:15:48 GMT

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.

Re: Peek Last record

Anonymous — Fri, 13 Nov 2015 09:41:15 GMT

I have made some changes at script level,

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

Re: Peek Last record

Anonymous — Fri, 13 Nov 2015 09:42:21 GMT

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

Re: Peek Last record

maxgro — Fri, 13 Nov 2015 09:56:16 GMT

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;

Re: Peek Last record

Fri, 13 Nov 2015 10:18:36 GMT

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)');

Re: Peek Last record

Kushal_Chawda — Fri, 13 Nov 2015 10:26:45 GMT

Create the straight table with Expression

=aggr(max(EditDate), OrderNo)

See the attached

Re: Peek Last record

Sun, 15 Nov 2015 13:33:56 GMT

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

Re: Peek Last record

Wed, 25 Nov 2015 11:12:58 GMT

Hairul

Change

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

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

Regards

Declan