9 Replies Latest reply: Nov 9, 2016 7:01 PM by Kamil Lewandowski RSS

    Load address from Excel then get Longitude/Latitude

      I've found a few sources on loading Excel data into QlikView:

       

       

      LOAD

           Address

      FROM

      C:\Users\username\Desktop\Addresses.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

       

      and a few other sources on getting the longitude/latitude values via Google GeoCoding:

       

       

      LOAD

      '$(C)' as Address,

      subfield([Response/Placemark/Point/coordinates], ',' ,1) AS latitude,

      subfield([Response/Placemark/Point/coordinates], ',' ,2) AS longitude

      FROM [http://maps.google.com/maps/geo?q=$(C)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [Sheet1]);

       

       

       

      ...but how would I do both? Meaning, how can I first load the addresses into QlikView, and then convert those addresses into longitude/latitude values? A subquery of some sort? Join the loads?

       

      Thanks for any help!