In the previous post I showed how additional GEO data could be loaded into your QlikView document based on IP address data already available.

This installment illustrates how additional GEO data can be loaded based just on place name data using the Yahoo! PlaceFinder API. The technique actually is almost identical, just needing to take into account the different XML schema returned from the PlaceFinder API which looks something like:

For the following request

http://where.yahooapis.com/geocode?q=Guatemala&appid=[yourappidhere]

You can then choose any of the additional data elements from the xml in your load script. For example in the following script I am loading in the latitude and longitude based on the place name:

((the 'latitude' argument in the above can be replaced with any of the XML element names in the Result element of the xml.)

In order for this to work, simply paste the code at the end of the post into the Macro module of your QlikView document, ensure JScript is selected as the 'Scripting Engine' and that the macro has system access.

You can also download the following (zipped QlikView) demo file which illustrates the technique:

Merging GEO Data from the Yahoo PlaceFinder API.zip (197.74 kb)

Don't forget that you should replace the APP_ID parameter in the JavaSctipt with your own which you can get from the Yahoo! site.

var 

    APP_ID = "SPECIFY_YOUR_APP_ID_HERE",

    placesVsData = {},

    xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"),

    xmlDoc = new ActiveXObject("Msxml2.DOMDocument.3.0");

function getDataForPlace(place, key) {

    if (placesVsData[place] == null) setUp(place);

    if (placesVsData[place].Err) {

        return placesVsData[place].Err;

    }

    else {

        return placesVsData[place].Node.selectNodes(key)[0].text;

    }

}

function setUp(place) {

    xmlhttp.open("POST", "http://where.yahooapis.com/geocode?q=" + place + "&appid=" + APP_ID, false);

    xmlhttp.send();

    if (xmlhttp.status != "200") {

        placesVsData[ip] = { Err: "Error (status = " + xmlhttp.status + ")" };

        return;

    }

    else {

        var res = xmlhttp.responseText;

        if (!xmlDoc.loadXML(res)) {

            placesVsData[ip] = { Err: "Error loading xml" };

        }

        else {

            var status = xmlDoc.selectNodes("/ResultSet/Error")[0].text;

            if (status == "0") {

                placesVsData[place] = { Node: xmlDoc.selectNodes("/ResultSet/Result")[0] };

            }

            else {

                placesVsData[place] = { Err: "Status = " + status };

            }

        }

    }

}

I recently wanted to combine some Geo information into a QlikView application I was working on based on the IP addresses which were already in my dataset. The solution turned out to be relatively straight forward and I thought I would share the approach I took which allows latitude, longitude, city name, country name and other info to be easily be loaded into a QlikView application based on IP addresses.

I am using http://ipinfodb.com which allows you to pass an IP address in the url, for example:

http://ipinfodb.com/ip_query2.php?ip=74.125.45.100&timezone=false

The response returns looks something like:

The approach I took was to write a JavaScript function to call this service which then allows me to use the following simple code in the QlikView load script:

In order to allow this to work simply paste the following JavaScript into your QlikView macro module, not forgetting to select JScript for the 'Scripting Engine' setting and also giving the macro system access.

var 

    ipsVsData = {},

    xmlhttp = new ActiveXObject("Microsoft.XMLHTTP"),

    xmlDoc = new ActiveXObject("Msxml2.DOMDocument.3.0");

function getDataForIPAddress(ip, key) {

    if (ipsVsData[ip] == null) setUp(ip);

    if (ipsVsData[ip].Err) {

        return ipsVsData[ip].Err;

    }

    else {

        return ipsVsData[ip].Node.selectNodes(key)[0].text;

    }

}

function setUp(ip) {

    xmlhttp.open("POST", "http://ipinfodb.com/ip_query2.php?ip=" + ip + "&timezone=false", false);

    xmlhttp.send();

    if (xmlhttp.status != "200") {

        ipsVsData[ip] = { Err: "Error (status = " + xmlhttp.status + ")" };

        return;

    }

    else {

        var res = xmlhttp.responseText;

        if (!xmlDoc.loadXML(res)) {

            ipsVsData[ip] = { Err: "Error loading xml" };

        }

        else {

            var status = xmlDoc.selectNodes("/Locations/Location/Status")[0].text;

            if (status == "OK") {

                ipsVsData[ip] = { Node: xmlDoc.selectNodes("/Locations/Location")[0] };

            }

            else {

                ipsVsData[ip] = { Err: "Status = " + status };

            }

        }

    }

};

There you have it! Simple augmentation of IP addresses with Geo data which can then be used to create maps and other selection objects in your QlikView application.