Data Duplication

[ajr1705]

I need to write a report to show only new customers. I need to run the report so that it runs for 2 periods and check who new customers are in the second period.

The problem I am facing is that the customers are some time duplicated and I need to exclude this.

I need to check by Name and postcode to see if they are duplicated.

E.g.

Table

Cust Nam C Date Amount

1 a qq Jan06 20
2 b ww Jan05 20
3 c rr Jan06 20
4 b ww Jan 07 20
5 d rr Jan07 20
1 a qq Jan07 20

I am looking for the result to be customer 5 in Jan 07

Any suggestion would be appreciated


V Cr 8.5
DB Oracle

 

[Madawc]

Group by name and postcode - have a formula field that combines them into a single group field.

With the data you show, you could show the new customers by a summary count and group selection to get rid of anything that counts more than 1. I think group selection was available in 8.5: if not, section suppression will do it.

Rather, you need a value of 1 and the period is current; a more complex text/

If customers can also have two entries in a period and this would count as new, then you'd need to group by period within name & postcode and count on that basis.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ajr1705]

Thank you for the reply, I have followed what have said, unfortunatley when i create a formula using the Group filed I cant group on the formula field nor can i count it.

 

[BOjdh2n]

Do you have the Option to "select distinct records" under the "Database" menu? If so, this may help you remove duplicate records from the data set.

 

[ajr1705]

I have I will try it, Thanks

 

[ajr1705]

Boss man,

I have done the following as suggested by you

grouped on name,postcode and also formula( name&Poscode).

name&Poscode
i am ristricted the data on group
count (formula( name&Poscode),formula( name&Poscode))=1

this gives me all new customers but when the post code is differnt or there are extra speaces in middle it shows the customer.

Is there any way to solve this.
I tried to trim and put case on it but can not get rid of spaces in the middlie of the post code)

Thanks

 

[Madawc]

You could replace double spaces in the postcode, i.e.

Replace({Pcode}, "  ", " ")

Use the resultant formula field. Replace triple spaces the same way

You could also use the start of the postcode, i.e.

Left({Pcode}, 3)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ajr1705]

Thanks for all the help guys, you guys are real stars.

Thanks again