topic Re: Assign Latest ID To Duplicate Records in QlikView

Assign Latest ID To Duplicate Records

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Assign Latest ID To Duplicate Records

Tue, 22 Aug 2017 09:31:54 GMT

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;

Re: Assign Latest ID To Duplicate Records

marcus_malinow — Tue, 22 Aug 2017 09:33:04 GMT

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

Re: Assign Latest ID To Duplicate Records

Tue, 22 Aug 2017 09:34:37 GMT

Try this in front end

=aggr(max(PersonID),Email)

Re: Assign Latest ID To Duplicate Records

omkarvamsi — Tue, 22 Aug 2017 09:39:13 GMT

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;

Re: Assign Latest ID To Duplicate Records

haymarketpaul — Tue, 22 Aug 2017 10:13:09 GMT

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