10 Replies Latest reply: Dec 26, 2013 8:43 AM by Lucian Cotea RSS

    Implementing (Google) maps in QV

      Hey everyone,

       

      I've been trying to implement maps in QV for a week now and I still haven't figured it out. I really hope you guys want to help me. I've followed the

      'Steps to Implement Google Map in Qlikview' document but I think it is outdated. Below you can see the code I am using in the script editor (I do have a gmap key). I will also attacht the .xlsx file and the qvw. My problem is with displaying a dynamic map (as a chart) and plotting the latitudes and longitudes on it. Can someone please explain to me how it is done properly? Many thanks in advance!

       

      - Bob

       

      PS: If someone thinks I should try and use open layer maps I'm very open to that, but I don't know how!

       

      SET ThousandSep='.';
      SET DecimalSep=',';
      SET MoneyThousandSep='.';
      SET MoneyDecimalSep=',';
      SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
      SET TimeFormat='h:mm:ss';
      SET DateFormat='D-M-YYYY';
      SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
      SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
      SET DayNames='ma;di;wo;do;vr;za;zo';
      
      CustomerTable:
      LOAD Customer, 
           City, 
           Quantity
      FROM
      [C:\Users\BJ\Documents\Building Blocks\QLIKVIEW\Google Maps handmatig\Customer_info.xlsx]
      (ooxml, embedded labels);
      
      let noRows = NoOfRows('CustomerTable')-1;
      
      for i=0 to $(noRows)
          let a = peek('Customer',$(i),'CustomerTable');
          let b = peek('City',$(i),'CustomerTable');
          let c = peek('Quantity',$(i),'CustomerTable');
             
          GeocodeResponse:
          LOAD
          status,
          '$(a)' as CustomerName,
          '$(b)' as CustomerCity,
          '$(c)' as CustomerQuantity,
          [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;
      
      // Google Maps Key
      gmap_key = '';
      max_zoom_level = 14;
      def_zoom_level = 1;
      def_map_size = 400;
      
      // Variables required for calculating map
      var_pi180        = '=pi()/180';
      var_lat_offset    = '0';
      var_mc2            = '=256*pow(2,$(var_zoom))';
      var_mc1            = '=256*pow(2,($(var_zoom)-1))';
      var_mid_lat        = '=median(latitude)';
      var_mid_long    = '=median(longitude)'; 
      var_zoom        = '=if(max(aggr(if(max(round(256*pow(2,(_zoom_level-1)))+(Longitude*((256*pow(2,_zoom_level))/360)))-min(round(256*pow(2,(_zoom_level-1)))+(Longitude*((256*pow(2,_zoom_level))/360)))<def_map_size AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<def_map_size,_zoom_level,null()),_zoom_level))>def_zoom_level,max(aggr(if(max(round(256*pow(2,(_zoom_level-1)))+(longitude*((256*pow(2,_zoom_level))/360)))-min(round(256*pow(2,(_zoom_level-1)))+(longitude*((256*pow(2,_zoom_level))/360)))<def_map_size AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<def_map_size,_zoom_level,null()),_zoom_level)),def_zoom_level)';
      var_maptype        = '=if(isnull(only(maptype)),fieldvalue('&chr(39)&'maptype'&chr(39)&',4),maptype)'; 
      
      // Calculate best zoom level
      set HidePrefix    = '_';
      
      _zoom_level:
      LOAD RecNo() as _zoom_level AutoGenerate(max_zoom_level);
      
      maptype:
      LOAD * INLINE [
          Maptype
          roadmap
          mobile
          satellite
          terrain
          hybrid
      ];