Duplicate records

ghilton · Post by **ghilton** » Mon Mar 23, 2009 12:49 pm

Hello

My marketing database which looks for duplicate email address is working fine but I do have a couple of issues

1. if an email adress is imported with 2 spaces at the end of the address and I have an exsisting email address which is the same but with out the 2 spaces on the end. AwareIM is not picking these up as duplicates? How can I get around this?
[email protected]
[email protected](Space)(Space)

2 Some emails are being imported with capital letters how would I search all records and make them lower case?

Thanks

tford · Post by **tford** » Mon Mar 23, 2009 5:03 pm

Have you tried TO_LOWER_CASE and REPLACE_PATTERN functions?

ghilton · Post by **ghilton** » Mon Mar 23, 2009 5:07 pm

Hi Tom

yes should of said! I have tried but do not really understand the syntax hence does not work!

tford · Post by **tford** » Mon Mar 23, 2009 5:09 pm

Try: BO.attribute_stripped=BO.attribute(REPLACE_PATTERN,` `,``)

ghilton · Post by **ghilton** » Mon Mar 23, 2009 5:21 pm

sadly not tom it does not like the brackets!

tford · Post by **tford** » Mon Mar 23, 2009 5:25 pm

Sorry for my transposed post

BO.attribute_stripped=REPLACE_PATTERN(BO.attribute,` `,``)

tford · Post by **tford** » Mon Mar 23, 2009 5:28 pm

BO.attribute_lower=TO_LOWER_CASE(BO.attribute)

ab042 · Post by **ab042** » Mon Mar 23, 2009 7:34 pm

Support: I've had an issue with Import as well.

Shouldn't IMPORT ALWAYS remove extra spaces at the end of a string?

aware_support · Post by **aware_support** » Mon Mar 23, 2009 10:58 pm

No, it shouldn't. It doesn't know whether it's intentional or not. If you want to remove them, remove them from the file.

ab042 · Post by **ab042** » Mon Mar 23, 2009 11:43 pm

Support: Intentional? What would one good example be of where we may want blank spaces at the end of a string?

I would agree it isn't AwareIM fault that spaces exist in the import file. But I would also say, in the real world 90% of the time you don't have the option on the export to remove them. And if you have a large file its not a simple load in excel and remove them. And if you have

It would be nice if it was a AwareIM import feature or even a default to not have them. Since I can't think of the last time I would have wanted to keep spaces at the end of a string field during an import.

In fact in the last 30 years of programming I don't ever remember wanting to keep spaces at the end of a string. And I can think of a lot of reasons I wouldn't want spaces at the end of a string.

Just my 2 cents. And at the end of the day we'll work with it. You have a great product and we Thank you for it.

ghilton · Post by **ghilton** » Tue Mar 24, 2009 9:16 am

I agree ab042, not sure why anybody would want spaces at the end of a string? In this case I am comparing 2 strings and marking one as a duplicate ready to delete (if indeed it is already in the database). Sadly the 2 spaces are affecting this so I manage to import duplicates.
As this BO is a marketing tool collecting emails and sending mail shots duplicates are really bad as we would then be sending multiple emails to one company which can be classed as spam which is not our intention!

Support can you put this on the wish list please?

Tom, thanks for the examples I will be working with them this morning and will let you know!

ghilton · Post by **ghilton** » Tue Mar 24, 2009 10:33 am

Hi Tom

Sorry but the rule cannot find BO.attibute_stripped and BO.attribute_lower?

In My Case -

Contacts.EmailAddress_stripped=REPLACE_PATTERN(Contacts.EmailAdress,' ',' ')

Contacts.EmailAddress_lower=TO_LOWER_CASE(Contacts.EmailAddress)

The lines produce the following -

rule '' uses attribute 'EmailAddress_stripped' which has not been found

rule '' uses attribute 'EmailAddress_lower' which has not been found

Cheers

tford · Post by **tford** » Tue Mar 24, 2009 11:48 am

graham,

those are example attribute names. you have to have attributes in any formula. either create them (using your own names)

ghilton · Post by **ghilton** » Tue Mar 24, 2009 12:03 pm

Hi Tom

I did hey give me some credit

. I have been playing around and am a bit confused

I created a process as below

Process1
Find Contacts Where Contacts.EmailAddress IS Defined
Process 2
Contacts.EmailAddress=TO_LOWER_CASE(Contacts.EmailAddress)

This had some wierd results. It ran and changed ALL the email address fields on all records to be the same as record 1? But at least is was all lowercase!

I removed this process and stuck the next line in the rules of the BO Contacts

Contacts.EmailAddress=TO_LOWER_CASE(Contacts.EmailAddress)

Now when I create/Import all the email address are now lower case as planned. But now the Duplication rule is ignoring the 2 spaces at the end of the address and marking them as duplicates? Great it now appears to be working?

Although this is working on the test system I am not sure why?

tford · Post by **tford** » Tue Mar 24, 2009 12:32 pm

I wasn't aware you could have the attribute name on both the left side and the right side of the = sign. Obviously you can since when you placed your rule in Contacts BO, it worked.

To have your rule fire for all Contacts, run a process with:

FIND ALL Contacts
UPDATE Contacts

This will to a one time cleanup of all existing Contacts.