1 Reply Latest reply: May 19, 2017 7:10 AM by Mohammed Mukram RSS

    By Using City Names get the longitude and latitude in Qlik Sense

    Mohammed Mukram

      Dears,

       

      i'm trying to show street level map in Qlik Sense.

       

      i have city names and street names but i don't have Longitude or Latitude

       

      i followed the following script in Qlikview to get the longitude and latitude by using city names,

       

      CustomerTable:
      LOAD * Inline
      [
      Quantity,City
      100,Hyderabad
      200,kolkata
      300,Mumbai
      150,banglore
      300,goa
      200,kerala
      ]
      ;
      let noRows = NoOfRows('CustomerTable')-1;
      for i=0 to $(noRows)
          let b = peek('City',$(i),'CustomerTable');

        GeocodeResponse:
        LOAD
        '$(b)' as City,
        ([result/geometry/location/lat]) as latitude,
          ([result/geometry/location/lng]) as longitude
        FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(b)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
      next i;

       

       

       

      long and Lat.PNG

       

      it's working fine to get the longitude and latitude but when i use the same script in Qlik Sense it's not working.

       

      Qlik Sense Script:

       

      CustomerTable:
      LOAD * Inline
      [
      Quantity,City
      100,Hyderabad
      200,kolkata
      300,Mumbai
      150,banglore
      300,goa
      200,kerala
      ]
      ;

       

      let noRows = NoOfRows('CustomerTable')-1;

      for i=0 to $(noRows)

          let b = peek('City',$(i),'CustomerTable');   

        GeocodeResponse:

        LOAD

        '$(b)' as City,

        ([result/geometry/location/lat]) as latitude,

          ([result/geometry/location/lng]) as longitude,

          GeoMakePoint([result/geometry/location/lat], [result/geometry/location/lng])

           AS [Longitude_Latitude]

          FROM [lib://GoogleAPI]

      (XmlSimple, table is GeocodeResponse);

      next i;

       

       

      not giving me the correct result.

      if anyone have any idea please help me to figure out the issue.

      (

      I Used the below URL while creating the GoogleAPI Connection

      http://maps.googleapis.com/maps/api/geocode/xml?address=$(b)&sensor=false

      )

      Thanks,

      Mukram.