topic Re: find latest in QlikView

find latest

Wed, 11 Jul 2012 06:01:19 GMT

Hi all,

again a question on a user table: The table Users is loaded from a DB and contains multiple records of one and the same user.

Example:

UserID	Name	email	RecordDate
111	Smith	smith@someemail.comm	2012-05-31
111	Smith	jsmith@someemail.comm	2012-06-14
111	Smith	smith@someemail.comm	2012-07-08

Now - during my LOAD - i want to make sure that i always only load the latest record of one user.

In this case, i would only want to load the last record for user 111.

How can this be done ?

Thanks!

Re: find latest

Anonymous — Wed, 11 Jul 2012 06:13:42 GMT

T1:

Load distinct

UserID,

Name,

from xxx;

join (T1)

Load UserID,

max(RecordDate) as RecordDate

From xxx

Group by UserID;

Or load the whole thing in one statement but then you need to group by UserID, Name and email (and all other fields you might have in the table )

Re: find latest

nagaiank — Wed, 11 Jul 2012 06:24:30 GMT

My two cents.

The second approach is desirable, as the name, email also might have changed. So load all the rows and get the latest name, email and date using FirstSortedValue function after grouping the rows by UserID.

Re: find latest

jagan — Wed, 11 Jul 2012 07:42:14 GMT

Hi,

You can do this by using GROUP BY option

TableName:

LOAD

UserID,

Name,

email,

RecordDate

WHERE flag = 1;

LOAD

UserID,

Name,

RecordDate,

If(Previous(UserID) <> UserID, 1, 0) AS Flag

FROM TableName

ORDER BY UserID, RecordDate Desc;

Hope this helps you.

Regards,

Jagan.