cancel
Showing results for 
Search instead for 
Did you mean: 

City Level information

Former Member
0 Kudos

Hello All,

I am new to Data Quality and currently trying to clean up our address information using DQ 3.2. We have a number of requirements to try and report on the city level information. For example sales by city.

Now here's the problem. Say they want to report on the city of Los Angeles. LA actually consists of a number of cities such as Beverly Hills, North Hollywood, Burbank etc. The word "Los Angeles" never appears anywhere in the address. We are doing this globally for all countries and I noticed a similar pattern in a number of other countries as well.

The big question: is there a field that i can select in the output of the address cleanse transform that actually gives me "Los Angeles"? Do note here that it should ideally apply for all countries. If this is possible, I could populate that in a separate field and have the users report on that field.

Thanks in advance!

Rohit

Accepted Solutions (1)

Accepted Solutions (1)

NielsWeigel
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Rohit,

the reference data used for address cleansing is in general from the country specific postal authority. For US it is the United States Postal Services. As USPS core purpose is to deliver letters or parcels to existing addresses they have set up their post code system. The boundaries of the post code regions some times do not map to the boundaries of other, even also official federal authorities like the municipalities. (See a [map of the Postcodes in the City of Los Angeles|http://lahd.lacity.org/lahdinternet/Portals/0/Policy/LAZipCodes.pdf] in the Internet)

Or the reference data suppliers do not include additional regional, neighborhood or community information, that would be helpfull for your granularity of mapping.

A workaround would be to include a Query Transform in your Job after Global Address Cleanse, where you have a lookup in an additional table e.g. with Postcode and Reporting Grouping Value (e.g. "City of Los Angeles") and populate this additional column based on the corrected cleansed address output from Global Address Cleanse.

The number of reporting groups could be huge, depending on industries, markets, ... Some companies want to go down to statistical information grouping just some building into one unit (Statistical Areas), others will just split a country into 4 or 5 regions based on multiple states (Nielsen Region). Most cases external lists are used to generate this additional field.

What other regions did you identify in your analysis?

Niels

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Niels,

Thanks for your response. Most of our reporting is at city level, so it is fine if we dont get to the actual doorstep. Your query transform idea sounds good though it would mean manually maintaining that lookup list that tells you which city/municipality is part of what metropolitan area. is this list readily available anywhere? Again, I have to think of a solution that could be used globally.

I still find it hard to believe that there isnt anything in the address cleanse transform that would have this information. Isnt this a problem most people doing any kind of reporting on cities would face?

Thanks,

Rohit

NielsWeigel
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Rohit,

the core purpose of Address Cleasning transform is validating, cleansing and correcting a given address. Sure you can imagine millions of additional attributes around an address (or in your case only a city) than can be appended.

You can take a look at the GeoCoder transform, where you get e.g. Metro_Stat_Area_Code or Stat_Area_code for US.

Niels