topic MaxDate and Max time selecting the data in QlikView

MaxDate and Max time selecting the data

Thu, 23 Jul 2009 02:42:32 GMT

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.

MaxDate and Max time selecting the data

johnw — Thu, 23 Jul 2009 03:26:20 GMT

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.

MaxDate and Max time selecting the data

disqr_rm — Thu, 23 Jul 2009 03:52:14 GMT

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;

MaxDate and Max time selecting the data

Thu, 23 Jul 2009 14:23:38 GMT

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

MaxDate and Max time selecting the data

johnw — Fri, 24 Jul 2009 00:13:43 GMT


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. 🙂

MaxDate and Max time selecting the data

rwunderlich — Fri, 24 Jul 2009 05:48:35 GMT


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

Nice solution!

-Rob

MaxDate and Max time selecting the data

disqr_rm — Fri, 24 Jul 2009 08:04:58 GMT

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

Rakesh