Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

johanru1
New Contributor

Creating map based on addresses in sql DB

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?

Labels (1)
1 Solution

Accepted Solutions

Re: Creating map based on addresses in sql DB

R9 is filtered but Exceptions and R3 doesn't have any 'where' clause to filter data.

You can use keep... (https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...)
...or exists... (https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/InterReco...)
... To filter data based in other tables loaded data.
12 Replies
Highlighted
Employee
Employee

Re: Creating map based on addresses in sql DB

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...

Ricardo Gerhard
OEM Solution Architect
LATAM
johanru1
New Contributor

Re: Creating map based on addresses in sql DB

Hi @dcj,

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?

Re: Creating map based on addresses in sql DB

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
New Contributor

Re: Creating map based on addresses in sql DB

Luckily that is all I need Smiley Happy

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

Re: Creating map based on addresses in sql DB

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

Captura.PNG

johanru1
New Contributor

Re: Creating map based on addresses in sql DB

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 Smiley Happy

No data.JPG

johanru1
New Contributor

Re: Creating map based on addresses in sql DB

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:
No data.JPG

Re: Creating map based on addresses in sql DB

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
New Contributor

Re: Creating map based on addresses in sql DB

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.

Community Browser