Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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 | 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
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
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;
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
Try this in front end
=aggr(max(PersonID),Email)
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;
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