topic Re: Counting and reporting duplicates. in QlikView

Counting and reporting duplicates.

Fri, 07 Feb 2014 07:42:06 GMT

Hello,

I have the following tables and I would like to count and report duplicates on each of those fields.

TableComm:

load

CellphoneNo as CellphoneNo_Comm,

EmailAdr as EmailAdr_Comm,

WorkTel as WorkTel_Comm,

HomeTel as HomeTel_Comm

From Commercial.qvd;

TablePers:

load

CellphoneNo as CellphoneNo_Pers,

EmailAdr as EmailAdr_Pers,

WorkTel as WorkTel_Pers,

HomeTel as HomeTel_Pers

From Personal.qvd;

TableNew:

load

CellphoneNo as CellphoneNo_New,

EmailAdr as EmailAdr_New,

WorkTel as WorkTel_New,

HomeTel as HomeTel_New

From New.qvd;

Re: Counting and reporting duplicates.

hic — Fri, 07 Feb 2014 08:16:47 GMT

You need to load all fields into the same table and check for duplicates using the Exists() function:

For each vTable in 'Commercial','Personal','New'

PhoneNumbers:

load

'$(vTable).qvd' as File,

RowNo() as Row,

CellphoneNo,

Exists(CellphoneNo) as CellphoneNo_Duplicate,

EmailAdr,

Exists(EmailAdr) as EmailAdr_Duplicate,

WorkTel,

Exists(WorkTel) as WorkTel_Duplicate,

HomeTel,

Exists(HomeTel) as HomeTel_Duplicate

From [$(vTable).qvd];

Next vTable

HIC

Re: Counting and reporting duplicates.

Fri, 07 Feb 2014 08:20:33 GMT

Hi Henric,

I cannot change the qvd(s) as this is a model that has already been built. I just need to add this function on.

Is there another solution?

Re: Counting and reporting duplicates.

hic — Fri, 07 Feb 2014 08:37:40 GMT

Not sure I understand you... Are you saying that you

Cannot change the QVDs? Then the answer is: But the script does not change the QVDs. Just run it and it will work.
Cannot change or run the script in the QVW? Then the answer is: You cannot make a good solution. What you can do is to create a chart with CellphoneNo_Comm and CellphoneNo_Pers as dimensions and If(CellphoneNo_Comm=CellphoneNo_Pers,'Duplicate','Non Duplicate') as expression. A solution that is inefficient and kludgy. Try to do it in the script instead...

HIC

Re: Counting and reporting duplicates.

ashwanin — Fri, 07 Feb 2014 09:01:14 GMT

Hi Zema,

Henric is much experienced in QV. and the script advised by him is Correct.

You just need to copy the script advised by him and paste the same in your QV along with your script,

then Reload the model . The fields suggested by him will provide you the Duplicate phone numbers and etc..

From where you can count the numbers also.

Re: Counting and reporting duplicates.

Fri, 07 Feb 2014 10:05:44 GMT

Use concatenation

Re: Counting and reporting duplicates.

Anonymous — Sat, 08 Feb 2014 13:57:35 GMT

Hello Henric,

Many thanks for this tip.

A question before I test the solution, can the Exists() function handle a composite key?

For instance, if the uniqueness is evaluated with DateOfBirth&'|&Age, would Exists(DateOfBirth&'|&Age) work "on the fly"?

Or would I have to pass by a resident load or a temporary table?

Many thanks,

Antoine

Re: Counting and reporting duplicates.

hic — Sat, 08 Feb 2014 16:14:04 GMT

You can use Exists() with two parameters: Exists(<Field>, <Expression>) and then it will compare the expression with an existing field. But it must have a field to compare with.

However, there is nothing that prevents you from creating this field

DateOfBirth&'|&Age as TestField

HIC

Re: Counting and reporting duplicates.

Anonymous — Sat, 08 Feb 2014 16:24:02 GMT

Thank you very much,

Antoine