Map - KML - US County

[jzimolong]

New to Qlik Sense (downloaded the free desktop version this past weekend), but the Map feature is just what we've been looking for.  What I've been trying to accomplish over the past 4 days is figuring out how to import a KML file, for US Counties, that automatically brings in the "name" as :

Two digit state - county name -  For example, it would bring in name = "NY-Erie"

Trying to avoid the common issue where you have the same county name all over the country.  Cannot figure out how to come up with a KML file that I can link to with the key of state-county ( NY-Erie ).  Each time I play around with a US Census KML file, it ALWAYS imports area.name = county name

. 

I am posting this discussion in hopes that someone can point me to a good link/suggestion that will explain how I can "modify" an existing KML file so Qlik Sense will bring in the "name" field as state-county, so I can then link my real estate data to the KML and generate the mapping visuals we've been looking for.

I hope this question isn't redundant, but I have been at this for several days and keep coming up empty.

Thank you in advance.

Joe

[reddy-s]

Hi Joseph,

Do you have the State and Country names in the Fact table or the dimension table you are trying to link the KML data on? If so I would suggest you concatenate those field and the use an applyMap() function on the KML file. So inthis way you will be able to Link them up.

[brunobertels]

Hi Sangram

Trying to answer to Joseph , i see that the problem is that some counties from different state have the same name. So unable to attached them to a unique state.

I tried to export from google fusion a kml file with geometric and and change the county name by the state-county name.

but it does'nt work.

bruno

[jzimolong]

That is correct.  When I view the KML file that I downloaded from :

Google Fusion Tables

It is exactly what I'm looking for.  Further, when I view the KML (XML) details, it definitely has the column I'd like to use for linking to my fact (dimension) table, which is "State-County" in the screeshot below.

The issue is when I import this KML file into Qlik Sense, it automatically pulls in the placemark <name> instead, which is just the county name itself.  This is what I can't figure out.  How can I force Qlik Sense to recognice the "State-County" from the KML file as the "name" to import for use in the Map, so I can then link to fact table.

Here is a screenshot of what it looks like when I import the KML into Qlik Sense:

I have experimented with updating the KML file manually, changing the placemark name.  An example would be "Erie" county, which exists in NY, PA and OH.  If I update the placemark name manually, setting each to NY-Erie, PA-Erie, OH-Erie, it works exactly how I want.  Problem is there are over 1700 cases where I'd have to manually update the KML file.  If I could just have Qlik Sense import the field that already exists in the KML file (State-County), I'd be good to go.

[brunobertels]

Hi Joseh

Have a look here https://community.qlik.com/docs/DOC-9227

and fallow this tutorial you may be able to solve your issue

I did it and be able to built this app attached

Surely to adapt to your own requierment

Regards

Bruno

[brunobertels]

Hi Again

Simpliest way to get conties by state:

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

[jzimolong]

Thank you for the responses Bruno, much appreciated.  I ultimately got this working by editing the KML file using a separate script program.

I downloaded the KML from here:

http://www2.census.gov/geo/tiger/GENZ2014/kml/cb_2014_us_county_5m.zip

Once downloaded, the script file then updated the KML file, as follows for each placemark id:

KML Before Script was run:

KML After Script was run:

Simply adding the State FIPS code "STATEFP" before the county name then made the placemark id unique per county, and this imported into my Qlik Sense application just the way I wanted.  I then added the State FIPS code "STATEFP" to my fact data table, and created a key field as STATEFP-County (i.e. "01-Franklin").  This linked over to the KML data and the Qlik Sense map was good to go.

I ultimately used the KML file from the site above because the boundary lines appeared more accurate in Qlik Sense.  Once I got my fact data linking properly, it turned out well.

I've attached the updated KML file if this could ultimately help someone else out in the future.

[Anonymous]

Hi Joseph, thanks so much for doing this - I was struggling with the exact same problem.  Would you mind sharing the script program and script that you used to modify it?

[rbecher]

Hi Joseph,

could you produce a KML where you set name to FIPS (GEOID)? That would be a better join..

- Ralf

[rbpeterson2]

the re-formatted file is great - but for some reason, it's not showing all of the counties when I load into Sense (2.0) ... any ideas why that would be?