getting latest record in script

[hopkinsc]

Hi All,

i am trying to create a flag for the latest record in the script;

I have an email address and Date, i want to flag the row with the latest date per email address.

i have attached an example, my real data has a lot more fields but Email is the key to the rest of the tables and its the date i need it sorted on.

can anyone help please?

[swuehl · Accepted solution]

Maybe like

Data:

Load * inline [

Email, Date

PersonA@Email.com, 01/01/2012

PersonA@Email.com, 01/02/2012

PersonA@Email.com, 01/05/2012

PersonA@Email.com, 01/09/2012

PersonB@Email.com, 01/01/2013

PersonB@Email.com, 01/08/2015

PersonB@Email.com, 01/04/2016

]

;

LEFT JOIN

LOAD Email, Maxstring(Date) as Date, 1 as Flag

RESIDENT Data

GROUP by Email;

[jonathandienst]

Data:

Load * inline [

Email, Date

PersonA@Email.com, 01/01/2012

PersonA@Email.com, 01/02/2012

PersonA@Email.com, 01/05/2012

PersonA@Email.com, 01/09/2012

PersonB@Email.com, 01/01/2013

PersonB@Email.com, 01/08/2015

PersonB@Email.com, 01/04/2016

]

;

Join (Data)

LOAD Email,

  Max(Date) as Date,

  1 as MaxFlag

Resident Data

Group By Email

;

[hopkinsc]

Thanks Stefan, i didn't think of doing something as simple as that, i have been trying to get it working using FirstSortedValue or applymap!

thanks!