Geocoding by IP Address using a QlikView Expressor Extension

    Recap

     

    For a refresher on what a QlikView Expressor (QVE) Extension is please see this article and tutorial:
    http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/14/extending-qlikview-expressor-with-the-new-extensions-sdk

     

     

    The attached extension object (bottom of post) is to be used with QlikView Expressor 3.9.x and can be installed and enabled in a QVE Workspace. The extension object has been built using QlikView Expressor Datascript and the new SDK Extension Builder. Please see the above mentioned link for more on building your own extensions.

     

    The Need

     

    A customer needed the ability to enrich their data sets with additional location information including gecoding those locations for mapping purposes. All they had as input was a list of IP addresses (from logs or DB queries) that were accessing their systems. They wanted this process to be reusable, repeatable and shareable with other applications and development groups.

     

    geo_ip_df.png

    The Extension

     

    The attached extension () is provided as a QlikView Expressor Transform Operator which accepts incoming IP addresses. It then geocodes them using a free RESTful web service API (http://freegeoip.net/) and provides the following additional location data:

     

    country_code
    country_name
    region_code
    region_name
    city
    zipcode
    latitude
    longitude
    metro_code
    areacode

     

    Please see http://freegeoip.net/ for information about the web service usage and limits.

     

    GeoIP.png

    The Extension Properties

     

    Custom properties for the extension simply include the ability to toggle the returned results in the Results pane as well as set a sleep time in between API calls. This may be necessary as the web service may have time period execution query limits on the number of results that are returned.

     

    geo_props.png

    Installing the Extension

     

    Follow the instructions outlined here:
    http://documentation.qlik.com/expressor/3.9/Extensions/Install_and_Enable_an_Extension.htm

     

    Using the Extension

     

    Note: Extension operators that do not use Connection or Schema artifacts will be in the "Templates" tab of the corresponding operator:

     

    geo_temp.png

     

    • Drag the GeocodeIPAddress operator over to the canvas after the input operator that returns the IP address data
    • Double click the operator to access the Rules Editor (or click Edit Rules in the property panel)
    • Simple take your incoming input field (ip) and connect to the "ip" parameter

     

    geo_transform.png

    • Connect the operator's output port to your write operator

     

    Notes

     

    • The attachments at the bottom of this document contains:
      • 1 zip file - The QVE project with extension descriptor library
      • 1 extension - The QVE Extension object (FreeGeocoderByIP.eex)
      • 1 data input file - Sample IP inputs (justIps.txt)

    • Previous knowledge of using QVE is recommended (see sample project in the .zip file for complete example)
    • Unzip QVEGeoIPExtensionAndProject.zip to a temp location
    • Install the extension
    • Do not unzip sampleGeocodeProject.zip- use the Import Projects tool from the QVE Workspace to import that project
    • Adjust your File Connection artifact (InputAndOuputDataFiles) to match the corresponding file path location of your drive

     

    The Results

     

    ext_sdk.png

     

    Michael Tarallo
    Senior Product Marketing Manager
    QlikView and QlikView Expressor
    Follow me - @mtarallo