Assign Latest ID To Duplicate Records

[haymarketpaul]

QV12 SR4

I am being supplied with a table (directly from SQL Server) like the following which essentially contains duplicated people hence the differing PersonIDs but same email address.

PersonID	Email	EventID	CompanyName
1	me@you.com	123	Company1
2	me@you.com	321	Company2
3	me@you.com	456	Company3
4	me@you.com	654	Company4

I would like to use the latest PersonID for each record so would end up with something like this which i can break down to extract EventIDs etc at a later stage:-

PersonID	Email	EventID	CompanyName
4	me@you.com	123	Company1
4	me@you.com	321	Company2
4	me@you.com	456	Company3
4	me@you.com	654	Company4

I'm thinking i could possibly use PEEK if i order the table correctly but have not had to do something quite like this before and perhaps i am missing a better way to achieve this.  Meanwhile i'll have a play with PEEK and see what happens.

Any help appreciated

[#2]

Try this in backend

Table:

Load *Inline [

PersonID, Email, EventID, CompanyName

1, me@you.com, 123, Company1

2, me@you.com, 321, Company2

3, me@you.com, 456, Company3

4, me@you.com, 654, Company4

];

left Join

load Email,

max(PersonID) as Psnid

Resident Table group by Email;

[marcus_malinow · Accepted solution]

Peek might work, if you order your data set in Email, PersonID descending order.

Alternatively you could use applymap.

MaxPersonIDMap:

MAPPING LOAD

    Email,

    max(PersonID) as maxPersonID

FROM [Your data source]

GROUP BY Email;

Then simply use

ApplyMap('MaxPersonIDMap', Email, '[Unknown]') as PersonID

[#4]

Try this in front end

=aggr(max(PersonID),Email)

[omkarvamsi]

Hi Paul

Please try below script

abc:

LOAD * INLINE [

    id, name, company

    1, a, cc

    2, a, cf

    3, a, cg

    4, b, cgg

];

maxidtable:

load max(id) as maxid

Resident abc;

LET vLatest=peek('maxid',0,'maxidtable');

load

$(vLatest) as ID,

name,

company

Resident abc;

drop table abc,maxidtable;

[haymarketpaul]

Thanks for replies all

Thirumal - backend solution works nicely - good to know

Marcus - went with ApplyMap as the correct answer as a little bit cleaner as i don't have to Left Join any additional fields