topic Re: How to solve problems with duplicate city names in Integration, Extension & APIs

How to solve problems with duplicate city names

Anonymous — Mon, 11 Dec 2017 14:49:40 GMT

Hi there,

I have a list of hotels that I want to visualize on a map. From the round 600 records only few are shown on the map. I did some checking on that and realized that as soon a city is duplicate it is not shown on the map.

Is there a chance to include postcode in order to have a unique id to show on the map. As far as I could see this option is not available but nevertheless I need a way to solve this.

Thx for your help

Florian

Re: How to solve problems with duplicate city names

Anonymous — Mon, 11 Dec 2017 14:54:37 GMT

For Example there is a city called "Bad Endorf". In this case it's found on openstreetmaps but not shown on the map in qlik sense..

Re: How to solve problems with duplicate city names

jonvitale — Mon, 11 Dec 2017 16:26:32 GMT

Florian,

Where are you retrieving the latitude and longitude from to place these cities on the map?

Re: How to solve problems with duplicate city names

uroboros — Mon, 11 Dec 2017 20:25:51 GMT

You need make a geoPoint on QlikSense, use this way: GeoMakePoint().

Indeed, for open street map, it works, but qlik sense uses one point to draw these geo location,

GeoMakePoint - script and chart function ‒ Qlik Sense

Re: How to solve problems with duplicate city names

Anonymous — Wed, 13 Dec 2017 07:43:26 GMT

Hi @miguel - do I have to use this as a formula field in the data table or can I make simple dimension. I've tried the second one but the formula only produces "-" values.

Sample original values Longitude latitude

34.0627000 -84.0833000

The value in the new formula field Geopoint is "-"

Whats wrong?

Re: How to solve problems with duplicate city names

uroboros — Wed, 13 Dec 2017 17:02:04 GMT

If you can do it from script it works better, on load script create the field geoPoint with the next sintax:

LOAD *
GeomakePoint(Latitude,Longitude) as geoPoint

From <Table>;

If you want, before to do the map, create a table to validate than the point be unique.

Next, load the layer for map with geoPoint, and label with the Hotel name. Share to us some data or .qvf to be more helpfull to you

Re: How to solve problems with duplicate city names

Anonymous — Wed, 13 Dec 2017 18:08:10 GMT

thx mate - I'll give that a try. In case I fail I#ll be happy to provide the qvf. I'm a complete newbe at qlik so I really appreciate your help!

Re: How to solve problems with duplicate city names

Anonymous — Thu, 14 Dec 2017 12:56:05 GMT

Well - I failed. I was not able to get all the subscribers from the list shown on a Map

Possible Fields are latitude, longitude or GP where I have both coordinates in one field. Further more I have the postcodes in field "Startort" and a field City well as Land or CC with the iso codes. I've put the qvf and a sample xls to a onedrive folder: https://1drv.ms/f/s!AgNAQ7Gmr6x7kZc1SYl98msZy1yStg

thanks for your help!

Re: How to solve problems with duplicate city names

uroboros — Thu, 14 Dec 2017 15:58:48 GMT

Hi Florian, I Understand better your problem now, you have so many ways to resolve:

1. Al you need is define a KML with a cities of your country - region; and use de Id of KML to set a layer on OpenStreet Map, it works too with post Code; but it'll not draw points, it draws regions with colors such like this image:

You have loaded KML for your country. you need find others for Cities, and it works fine.

Loading map data ‒ Qlik Sense

2. You can convert the post codes or adress to lat and long in diferents ways, if you want to use it use this two:

https://www.latlong.net/convert-address-to-lat-long.html

Convert Addresses to GPS Coordinates

Or use this aplication in QlikView:

https://community.qlik.com/servlet/JiveServlet/download/202254-33523/google-maps-address-data.qvw

In this case you can use points.

3. If finally don't works, change object to an extension with SVG or Google maps. On Qlik Branch you can find it:

Qlik Branch

There you can download, and install them.

NOTE: If you can generate or find SVG to the map, it works with a better performance.

http://branch.qlik.com/#!/project/56728f52d1e497241ae69868

I hope it helps you.

Regars

Re: How to solve problems with duplicate city names

Anonymous — Mon, 18 Dec 2017 10:53:09 GMT

Hi Miguel,

got you. One last question - how does a geopoint field has to look like, when containing lat and long

Just LAT, LONG or does the field have to contain any kind of brackets?

Best

Florian

Re: How to solve problems with duplicate city names

uroboros — Mon, 18 Dec 2017 15:36:49 GMT

Hi Florian, It should be a number (INT or double, prefered double), and to do a geo point it should be something like this:

makegeoPoint(0.32,8.75)

For example.

Regards.

Miguel