Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Compare Addresses

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Compare Addresses
 Login/Join
 
Virtuoso
posted
I should be able to do this...but I'm just not getting it. I've tried holding and matching and nothing is working right.

I have a Table that has
Vendor
Account Number
Name
Name2
Street
City
Zip

These are apparently Free Entry Fields so someone can put something and it will generate a new Account number.
John Jones
123 St John Street
Houston
Tx
77096

Then someone else can come in...and not see this and set up a new one

John Jones
123 Saint John St
Houston
Tx
77096

Then yet another
J Jones
123 St John Street
Houston
Tx
77096


So you then have 3 things meaning the same thing with 3 different account numbers

I need to be able to take these three things and print them out in a report as potential duplicates.
I've tried things with CONTAINS or LIKE and holding and matching...but I just can't seem to get both instances to appear in a report correctly. Anyone have some ideas...

This message has been edited. Last edited by: Kerry,


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
Data scrubbing/cleansing is a huge subject completely outside WebFOCUS - you should see all the services offered, e.g. Qualified Address: Standard List Scrubbing. Google may help find some scrubbing suggestions.

What are you testing in the CONTAINS or LIKE? How would you expect the names or addresses to be selected as potential duplicates? You may have to compare individual substrings of the whole string. You may want to strip out the words "Street", "St", "Road", "Rd", "Boulevard", "Blvd", etc from the address string before comparing. You may be able to use the SOUNDEX character function...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
Dirty data...tisk tisk.

If it were me, and the database relatively small, I'd sort the data by the zipcode, then by the address, then by name and give it to someone in MS Excel to look through and highlight all the potential problems or something.

If it were a larger database, I'd look into a SOUNDEX function which compares words based on how they sound rather than they are spelled for the Names and addresses.

Wish you the best, projects go from fun and exciting to mundane and disgusting based entirely on quality of data, IMHO.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Virtuoso
posted Hide Post
thanks Francis...I'll take a look at those.
Jason we are talking a huge amount of data.

I was trying doing a compute making a Flag if NAME EQ LAST NAME and holding this data in a hold file then trying to do a MATCH back to the original to get both records but it is only giving me the one. I've tried OLD NEW OLD-NOR-NEW. I thought it should be After MATCH HOLD AS NEW. Which should take all the records in my New file...and match with the old...and print both. But I'm not getting that.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
quote:
SOUNDEX function

SOUNDEX function

Thanks Jason I was not familar with that Function...looks promising...
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Virtuoso
posted Hide Post
We use SOUNDEX in several applications where we compare a policyholder name from our file with a company name submitted from external sources. Also for claimant name comparisons. It has proven to be pretty reliable as far as finding a lot of potential duplicates, but it still seems to miss a lot of others. Gets us part way there, which is better than nothing. You might also try using GETTOK to separate the first/middle and last names and compare with SOUNDEX to achieve better results.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Prarie,

My first thought was to separate out the last name, then sort by zipcode and lastname, and detect duplicates (WHERE TOTAL GT 1) for output.

Look through the duplicates file for real duplicates versus coincidence.


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Virtuoso
posted Hide Post
Yes I have to separate out the last name...and I think sorting and using SOUNDEX is going to get me where I need to be. I love learning something new. Smiler
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Compare Addresses

Copyright © 1996-2020 Information Builders