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.

     

    Hope you enjoy it and sorry for writing like I'm writing an SMS.

     

    Hi mto, can you give us your opinion about this and especially the problems related here ? Thank you !

     

    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