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 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):






        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:



        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,


    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"



    => After the "Drill Down"




    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