find latest

[#1]

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!

L

[Anonymous · Accepted solution]

T1:

Load distinct

     UserID,

     Name,

     email

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 )

[nagaiank]

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.

[jagan]

Hi,

You can do this by using GROUP BY option

TableName:

LOAD

     UserID,

     Name,

     email,

     RecordDate

WHERE flag = 1;

LOAD

     UserID,

     Name,

     email

     RecordDate,

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

FROM TableName

ORDER BY UserID, RecordDate Desc;

Hope this helps you.

Regards,

Jagan.