Jump to content

Pattern Count?


This topic is 2903 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a table (A) with a field in it with the address as a string.  There are 210,000+ records

 

In another table (B), I have 3 fields that make up part of the basis of the address as per above (state, postcode, suburb).  This is an official list of postcodes and suburbs

 

What I would like to do is do some sort of pattern count that would identify where one of the suburbs appeared in the legacy field in Table A and replace the three separate fields (state, postcode, suburb) with the appropriate information.

 

i.e

 

Table A

Legacy Text = 28 Whatever Street, Brisbane QLD 4000

Suburb = {TBC}

State = {TBC}

Postcode = {TBC}

 

Table B

Suburb

State

Postcode

 

Link Table A to Table B and where a Suburb is found in the Legacy Text, insert Suburb, State, and Postcode as appropriate.

 

The address in the Legacy table can be a mix of data (i.e some don't have postcodes, have lots of rubbish written in etc etc.  So I need to only match where Suburb in Table B matches a word in Legacy Text in Table A.

 

I tried to do a join where PatternCount(Legacy Text; {Join Table B Suburb})  to no avail.

 

TIA

Link to comment
Share on other sites

So I need to only match where Suburb in Table B matches a word in Legacy Text in Table A.

 

In order to do that, you need to split the words in Legacy Text into return-separated values and use the result as the matchfield in a relationship to the other table, opposite Suburb. Keep in mind, however, that a match on any word is a match:  for example, if a street name in Legacy Text happens to match a suburb name, you will have a false positive.

 

Note also that this assumes that each suburb is a single word.

Link to comment
Share on other sites

This topic is 2903 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.