Counting and reporting duplicates.

[#1]

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;

[hic · Accepted solution]

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

[#3]

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?

[hic]

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

1. Cannot change the QVDs? Then the answer is: But the script does not change the QVDs. Just run it and it will work.
2. 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

[ashwanin]

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.

[#6]

Use concatenation

[Anonymous]

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

[hic]

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

[Anonymous]

Thank you very much,

Antoine