topic Re: find duplicate in QlikView

find duplicate

Fri, 06 Jul 2012 14:07:36 GMT

hi all,

this might be a simple one:

in a user-table i have one field "Email".

In a first step: I want to identify all email addresses that have been used more than once.

In a second step: I want to count the number of appearances per email address.

Thanks for some hints!

Re: find duplicate

Fri, 06 Jul 2012 14:18:20 GMT

Sorry, i was not clear on this one:

I want to do this in the LOAD script.

Re: find duplicate

nagaiank — Fri, 06 Jul 2012 14:37:28 GMT

If UserFile has UserID and Email, the EmailFile (see the script below) will give details of emailCount and UserIDList

UserFile:

LOAD UserID,Email,... from ...(your datasource);

EmailFile:

load Email,Count(UserID) as EmailCount, Concat(UserID,'|') as UserIDList

resident UserFile Group By Email;

Re: find duplicate

Anonymous — Wed, 23 Apr 2014 22:14:31 GMT

Hello NagaianK:

I was looking at your instructions regarding this post. How would I be able to filter the List Box to show only those 'email addresses' that have been used MORE than once?

Thanks

David

Re: find duplicate

Anonymous — Wed, 23 Apr 2014 22:26:43 GMT

Hi again NagaianK:

Just to clarify the earlier email, I have adapted your solution to my own situation of course. My script purpose is to identify doctors who have the same name. The partial script is as follows:

[DOCTORS]:

Load DocNbr,

DocName,

Doc Inits,

DocName & ' ' & DocInits AS DoctorName,

[DOCCOUNTERS];

Load DoctorName,

Count(DoctorName) as DocCount,

Concat(DoctorName, '|' as DupList

resident DOCTORS group by DoctorName;

In the ListBox, I get the duplicated DoctorNames as well as those that are unique. My question is how to display only the duplicates?

Thanks

David

Re: find duplicate

nagaiank — Thu, 24 Apr 2014 14:34:55 GMT

Probably, you may create a listbox for the expression

If(DocCount > 1,DoctorName)