Is there a way to pre-scrub location information for correctness?

[kevinpintokpa]

We're trying out GeoAnalytics 5.8.1 with some success at getting area and bubble maps.

As is usually the case, our address book data is messy, often containing missing information or subtly misspelled information, e.g. Carrolton, Texas instead of the correct Carrollton, Texas.  GeoAnalytics displays the second correct form, but not the first incorrect form.

Is there a way to scrub my entire address book against the Lookup service and obtain a list of addresses that result in a lookup failure?  The error message in the map is not useful - it only says "Unsuccessful location lookup" without mentioning the name of the location.  In any case, it would be painful and hit and miss to try to find incorrect addresses this way.

The fields I am most interested in are City, State, Postal Code and Country.  If I can scrub these four fields, we can start to fix them in the source data.  We have about 8,000 addresses to scrub.  Most appear to be correct, but many are incorrect.

Ideally, I can write code in the script and pass in a long list of rows of these four fields and receive back notification about which rows failed to lookup.

Any and all ideas are appreciated.  We have the local map server installed, if that helps.

[Patric_Nordstrom]

Hi Kevin,

There are two Geo Analytics operations you can use, one for checking areas and one for checking points:

• NamedAreaLookup – Get the polygons for named areas such as countries, administrative areas and postal areas.
• NamedPointLookup – Get the (center)points for named areas such as countries, administrative areas and postal areas.

You will get a resulting a table with the matching records so you can find and correct the bad ones.

When supplying info to the lookup, you can improve the results by providing additional location and type information. See the documentation for more info about the location service. here's two example how to refer to cities and postal codes (XPC indicates xip info and P* is place info).

Cities:

=city:name & ',' state_abbr & ',US:P*'

Zip codes:

=zip_code & ',US:XPC'

Another good op is the "Load" operation, you can for instance load all the counties in the us to check the spelling of the county name.

Links to doc:

Qlik | Help

GeoAnalytics Reference

Location Services

Connector operations examples « bi.idevio.com

Thanks,

Patric Nordström

Qlik

[kevinpintokpa]

Hi Patric,

I downloaded the Qlik Sense app and installed and licensed Qlik GA for Sense. 

I downloaded the AddressPointLookup Example

I can open the app in Qlik Sense and it shows the validated map OK.

When I reload the document, I get this error:

The following error occurred:

Connector reply error: QVX_SYNTAX_ERROR: The GeoCode server responded with error: 403, Forbidden

The error occurred here:

?

Data has not been loaded. Please correct the error and try loading again.

The GA connector is set to use the GeoAnalytics server https://ga.qlikcloud.com and the connection tests successfully.

Any ideas?

Thanks in advance,

Kevin

[slondono]

Hi Kevin

As far as i know AddressPointLookup is not available yet. According to this The geocode server responded with error:403 Forbidden

[kevinpintokpa]

Hi Patric ,

I downloaded and tried the NamedPointLookup-example today.

A reload did not return any rows.  Is the service working properly?  Here is the log file:

2017-11-16 13:47:37 0077 [LookupResult]:
2017-11-16 13:47:37 0078 SQL SELECT [city], [cities_Geometry], [cities_Name], [CountryIso2], [cities_Adm1Code], [cities_Adm2Code], [LocationDbType] FROM NamedPointLookup(nameField='city', type='P*', country='US', nameTable='cities')
2017-11-16 13:47:37 0079 DATASOURCE cities INLINE tableName='data', tableFields='id,city', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {id city
2017-11-16 13:47:37 0080 1 Skokie,Illinois
2017-11-16 13:47:37 0081 2 Huntsville,Alabama
2017-11-16 13:47:37 0082 3 Boulder,Colorado
2017-11-16 13:47:37 0083 4 Baytown,Texas
2017-11-16 13:47:37 0084 5 Los Anxeles,California}
2017-11-16 13:47:37 0085
2017-11-16 13:47:37      7 fields found: city, cities_Geometry, cities_Name, CountryIso2, cities_Adm1Code, cities_Adm2Code, LocationDbType,
2017-11-16 13:47:37      0 lines fetched

[Patric_Nordstrom]

Thanks for notifying, we will look into it.

[Patric_Nordstrom]

Hi Kevin,

The example wasn't working due to a bug, it will be fixed.

We have updated the example now, so please download and try again.

In the updated example the country code is included in the input string:

Skokie,Illinois, US

Thanks,

Patric