Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Geographic 'Drill Down' (Areas and Points) for US MAP

YoussefBelloum
Esteemed Contributor

Geographic 'Drill Down' (Areas and Points) for US MAP

Hi Everyone,

Yesterday I tried to help on a Thread related to Drill down on maps using multiple geo-dimensions

=> Need help in creating maps in Qliksense

and I found a lot of questions on how to configure and display maps on Qliksense on the community, so for newcomers/Geo analytics lovers who want to have a working example, here it is !

I choosed the US map because it is the map that the thread talks about, and fortunately it is a BIG map, so the result is very clear !

The Idea is to exploit all the geographical restitution capablities of Qliksense (Points and areas) on a single App.

Ready to use clean Mapped geographic dimensions are difficult to find, so i made this example with what i found..

PS: you will easily find all the maps and levels you want on gadm.org BUT for huge maps like the US, KML will not work directly because the precision is very high (even the advanced geo functions like GeoReduceGeomety didn't work for me ), for that case you will need extra tools like ArcGis..

So i found kind of clean tables:

1. States/Cities tables (with Coordinates)

2. States table with Polygon (Areas)

I started to create the Points with the GeoMakePoints function on the first table (State/cities coordinates table):

City_points:

LOAD

    State,

    City,

    TextBetween(Location,'','N')&'000' as latitude,

    '-'&TextBetween(Location,' ','W') as longitude,

    GeoMakePoint(TextBetween(Location,'','N')&'000', '-'&TextBetween(Location,' ','W')) as geopoints  

FROM [lib://kml files/Copie de List of cities in US.xlsx]

(ooxml, embedded labels, table is [List of cities in US]);


=> the extra code is to purge/normalize the Location Field (because it was not usable), the only thing important here is the  GeoMakePoint function


2nd step is to load the second table to add the Areas:


states_area:

LOAD

    TextBetween("cb_2016_us_state_500k.Name",left("cb_2016_us_state_500k.Name",15), right("cb_2016_us_state_500k.Name",12)) as State,

    "cb_2016_us_state_500k.Area"

FROM [lib://kml files/cb_2016_us_state_500k.kml]

(kml, Table is [cb_2016_us_state_500k/cb_2016_us_state_500k])

where  not match(TextBetween("cb_2016_us_state_500k.Name",left("cb_2016_us_state_500k.Name",15), right("cb_2016_us_state_500k.Name",12))

,'Alaska','Guam','Commonwealth of the Northern Mariana Islands','Hawaii','Puerto Rico','United States Virgin Islands') ;

=> The extra code is to purge the State name field, the where condition is to remove Hawaii, alaska etc, to have a well centered map.

The joining key is the State


=> Choose the Map chart => choose the State or the City => choose between Areas or Points (try not to mix States with points and Cities with ares, because we do not have this information and the map will not work)


I tried to exploit the information of the city number, so I made city count by State analysis

Here is the result:

=> Before the "Drill down"

Screen1.png

=> After the "Drill Down"

Screen2.png

Like this we have Maps interacting together no matter which one we use

You will notice that I always put "Drill Down" between double quotes.. it is because I wanted to use a real drill down on a single map.

In Qlik logic, we can do that, I created the hierarchy master dimension (States - > City) and add it to the map, the first dimension is displayed correctly but when we want to drill down to cities, a message appears saying that the Hypercube is very big.. so maybe it is an issue ? mto

You can find the the sources and the QVF attached

You can do more interesting things if you combine this with extensions.

QlikSense version: November Release

Attachments
Version history
Revision #:
1 of 1
Last update:
‎01-09-2018 08:27 PM
Updated by: