MaxDate and Max time selecting the data

[#1]

Data looks like below

DocumentNo	Date	Time	Status
111	20090326	131022	STAT1
111	20090326	130907	STAT2
111	20081123	112200	STAT3
111	20081122	102000	STAT4

I have to find the latest status of the document - that is

111

20090326

131022

STAT1

tmp:

Load * from c:\file.xls;

Tmp1:

Load document,

Max(Date) as Udate,

Max(Time) as Utime

resident tmp

group by document ;

is working fine and it is picking only one value.

If I include the Status - it is picking the multiple records.For reporting I would like to show only the Latest value

111

20090326

131022

STAT1

Is their any way to pickup the max date and time along with status.

Advance thanks for your help.

[johnw]

Here's one approach. Load in your table sorting by DocumentNo, then Date and Time descending. Whenever the DocumentNo changes (compare to previous(DocumentNo)), set a flag to 1, else null. Then inner join to an inline table with the flag set to 1. Drop the flag.

There's probably a way to do it in a single load without the inner join, but I'm not thinking of it. I'm probably missing something obvious, and am about to be publically humiliated. 😉

And while it won't cause problems for my proposed approach, note that max(Date) + max(Time) is not the same thing as max(Date + Time). It only works in your example because your times increase along with your dates.

[disqr_rm]

Would this work for you?

load
date(max(timestamp#(Date & ' ' & Time, 'YYYYMMDD hhmmss'))) as MaxDate,
time(max(timestamp#(Date & ' ' & Time, 'YYYYMMDD hhmmss'))) as MaxTime,
maxstring(dual(Status, timestamp#(Date & ' ' & Time, 'YYYYMMDD hhmmss'))) as MaxStatus
resident tmp
group by 1;

[#4]

Hi,

what you can also do is to sort your table and use PEEK.

ActualStatus:
Load
DocumentNo,
Date as ActualDate,
Time as ActualTime,
Status as ActualStatus
Resident TempStatus
Where DocumentNo <> Peek(DocumentNo)
Order By DocumentNo, Date DESC, Time DESC;

See the attached example.

Rainer

[johnw]

John witherspoon wrote:There's probably a way to do it in a single load without the inner join, but I'm not thinking of it. I'm probably missing something obvious, and am about to be publically humiliated. 😉

Rainer Filoda wrote:Where DocumentNo <> Peek(DocumentNo)

Yeah, there we go. That's the obvious solution I was missing. 🙂

[rwunderlich]

Rainer Filoda wrote:Where DocumentNo <> Peek(DocumentNo)
Order By DocumentNo, Date DESC, Time DESC;

Nice solution!

-Rob

[disqr_rm]

wow! pretty cool solution Rainer. Thanks for the idea.

Rakesh