Creating map based on addresses in sql DB

[johanru1]

Hi,

I am trying to create a map based on addresses we have in a SQL DB.

Is there any way of doing this?

PS. I do see the massive issue with spelling errors and so on, but hopefully someone has figured out a smart way of getting around that issue as well.

 If none of this is possible, I would like to see if I can pull the postcodes directly from our SQL and get the Latitude/Longitude from a separate spread sheet?

[Ricardo_Gerhard]

Hi Joahn,

Qlik will show the points based on the source database. If you have incorrect speeling or address, you need to fix it previously.

Take a look on this topic in order to understand the Map chart.

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Visualizations/Map/M...

[johanru1]

Hi @Ricardo_Gerhard,

And thank you for the swift reply.

I seem to have all the parameteres I need, and the postcodes (Which it looks like I will be using are correct).

I'm just having trouble making Qlik understand that these are Geo points.

All the postcodes are located in Norway and would love it if I could limit it to pull from one table, but if needed, I can pull the ISO name or Country name from a few steps into some other tables.

Is there a specific geopoint I need to set for Qlik to interpret the data correctly?

[rubenmarin]

Hi,

So far I know you can only go to ZIP (postal code) or city/place level with default maps. To go to street level you will need the coordinates.

A software like Geocoding (https://bi.idevio.com/products/qlik-geocoding) can help to retrieve the coordinates of the addresses.

[johanru1]

Luckily that is all I need 🙂

I just can't seem to get Qlik to interpret the fields correctly.

[rubenmarin]

I'm not sure if I have understand your issue... maybe adding a field to set location type and/or country?:

[johanru1]

Hi again,

The problem is that I don't even get an option to add a dimension at the current stage.

I might be lacking some really basic knowledge here to be honest, so feel free to dumb it down for me 🙂

[johanru1]

As a side question, would the ZIP (Postal Code) need to look like this: NO-4809 f.ex?

At the moment my codes are following Norwegian standard with the country code in it, but City name is present.

Edit: Tried just pulling data from our country table, and even that did not work:

[rubenmarin]

There is a flag for field that says wich of them are dimensions, and wich also are suitable to use as geodata, this values are shown in the Add dimension panel of the map chart.

Also, using the 'Add layer' button you can set other fields to use as dimension, in the layer confguraction you will see something like my last screenshot, in 'Data' section you can set wich dimension use to locate the point/area..., Postal code field will go here.

You will need to split the data into postal code and country, this can be done in source database, script editor or using data manager. It depends on how you are loading the data.

in example, using script editor, if ZIPCode field has values like 'NO-4809' it can be:
Subfield(ZIPCode,'-', 1) as Country, // Stores 'NO'
Subfield(ZIPCode, '-', 2) as [Postal code] // Stores '4809'

[johanru1]

Thank you so much for your help.

Since my Qlik is not recognizing any of my fields as geopoints, I have ended up back with my spreadsheet that contains the Longitude/Latitude.

The issue I am facing now, is that it pulls all the data, even though I believe I have told it to only pull data if certain critieria is met.

My current code:

[R9]:
LOAD 
	[CustNo] AS [CustNo],
	[St] AS status,
	[R1] AS avdeling,
	[R3] AS Anlegg;
SQL SELECT "CustNo",
	"St",
	"R1",
	"R3"
FROM "F0001"."dbo"."R9"
where st = '3';

Exceptions:
LOAD 
	[POSTNR] AS [PNo-POSTNR],
	[Latitude] AS [Latitude],
	[Longitude] AS [Longitude],
	GeoMakePoint([Latitude], [Longitude]) AS [Longitude_Latitude];
LOAD [POSTNR],
	[Latitude],
	[Longitude]
 FROM [lib://AttachedFiles/postnummer.xlsx]
(ooxml, embedded labels, table is postnummer);

[R3]:
LOAD 
	[PNo] AS [PNo-POSTNR],
	[PArea] AS [PArea],
	[RNo] AS Anlegg;
SQL SELECT "PNo",
	"PArea",
	"RNo"
FROM "F0001"."dbo"."R3";

What I really want, is to only pull data where st = '3'.

I really thought I had some sort of knowledge regarding this, but seems hopeless at the moment.