topic Re: implement google map in QlikView

implement google map

thanhphongle — Mon, 15 Jun 2015 11:44:09 GMT

Hello community,

i tried to implement google maps in qv with an instruction posted in qv forum. unfortunetly i coulnt managed it and do not really know where my fault is. could anyone help me out here please?

my script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

LOAD [Lease No],

Category,

Manufacturer,

Model,

[Serial Number],

[Selling price],

[Location of installation],

FROM

[Testdatenbank 15.06.2015.xlsx]

(ooxml, embedded labels);

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

for i=0 to $(noRows)

let a=peek('Category',$(i),'Sheet1');

let b=peek('Manufacturer',$(i),'Sheet1');

let c=peek(‘Model’,$(i),'Sheet1');

let address=peek('[Location of installation]',$(i),'Sheet1');

Data:

LOAD

'$(a)' as Category,

'$(b)' as Manufacturer,

'$(c)' as Model,

'$(address)' as Standort,

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

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

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

new tab (Google map)

// get a key here http://code.google.com/apis/maps/signup.html

gmap_key = 'xx';

max_zoom_level = 17; //maximum value 17

// Variables required for calculating map

// No need to change these

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= '=min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)';

var_mid_long= '=min(longitude)+(max(longitude)-min(longitude))/2';

var_zoom= '=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)) ) <map_size_x 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()))))<map_size_y,_zoom_level,1),_zoom_level))';

var_maptype= '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1 ),maptype)';

map_size_x= '400';

map_size_y= '400';

SET HidePrefix='_' ;

// Field required for calcualting best zoom level

_zoom_level:

Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);

maptype:

LOAD * INLINE [

maptype

roadmap

mobile

satellite

terrain

hybrid

];

Re: implement google map

jpenuliar — Mon, 15 Jun 2015 12:57:07 GMT

Thanh Phong Le,

If you can attach your sample work, the community might be able to help you faster

Re: implement google map

thanhphongle — Mon, 15 Jun 2015 15:43:08 GMT

I've uploaded my qvw doc and my database

Re: implement google map

jpenuliar — Mon, 15 Jun 2015 16:13:26 GMT

try the attached script

Re: implement google map

thanhphongle — Mon, 22 Jun 2015 13:22:30 GMT

It returns error

Table not found

STANDORT:

Load Distinct

[Location of installation]

Resident Sheet1

Re: implement google map

thanhphongle — Mon, 22 Jun 2015 14:03:35 GMT

my script

MAP_PV:

Mapping LOAD

Category,

avg_PV_for_Category

FROM

[B-Basisdaten.xlsx]

(ooxml, embedded labels, table is Kategorien);

MAP_Zins:

Mapping LOAD

Periode,

Zins

FROM

[B-Basisdaten.xlsx]

(ooxml, embedded labels, table is Zinsdaten);

Data:

LOAD *,

([Payment (Asset) p. m.]/[Selling price])*100 as Leasingfaktor, //created field Leasingfaktor

Applymap('MAP_PV', Category) as avg_PV_for_Category,

Applymap('MAP_Zins',[Lease start date]) as Zins;

Directory;

LOAD [Lease No] as Kaufschein,

Position,

Subposition,

Manufacturer as Hersteller,

[Serial Number],

Category,

Model as Anlagebezeichnung,

[Lease start date],

[Scheduled lease end date],

[Actual lease end],

[Cost centre (asset)] as Kostenstelle,

[Location of installation],

[Selling price],

[Payment (Asset) p. m.],

Currency,

Leasinggeber as Region,

Technologie as Kategorie

FROM

[A-Import_LPM.xlsx]

(ooxml, embedded labels, table is Sheet1)

where [Payment (Asset) p. m.]>0;

//1.10 B

let vAuswertungsdatum = today();

//1.10 E

Join (Data)

LOAD *,

If(AvgLeasingfaktor>=1.5 and AvgLeasingfaktor < 2.0, '60 Monate',

If(AvgLeasingfaktor>=2.0 and AvgLeasingfaktor < 2.4, '48 Monate',

If(AvgLeasingfaktor >=2.4, '36 Monate','Festkontraktierte Verlängerung')))as Zuordnung;

LOAD Category,

Zins,

Avg(Leasingfaktor) as AvgLeasingfaktor

Resident Data

Group By Category,Zins;

Directory;

LOAD [BD Category],

[BD LZ],

[BD FV],

[BD PV],

[BD Konstante in %]

FROM

[B-Basisdaten.xlsx]

(ooxml, embedded labels, table is [Kategorien BD]);

STANDORT:

Load Distinct

[Location of installation]

Resident Data;

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

for i=0 to $(noRows)

let a = peek('Location of installation',$(i),'STANDORT');

LOAD

'$(a)' as Standort,

Replace([result/geometry/location/lat], '.', ',') as Latitude,

Replace([result/geometry/location/lng], '.', ',') as Longitude

FROM [https://maps.googleapis.com/maps/api/geocode/xml?address=$(a)] (XmlSimple, Table is [GeocodeResponse]);

next i;

var_mid_lat= '=min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)';

var_mid_long= '=min(longitude)+(max(longitude)-min(longitude))/2';

var_maptype= '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1 ),maptype)';

map_size_x= '400';

map_size_y= '400';

SET HidePrefix='_' ;

// Field required for calcualting best zoom level

_zoom_level:

Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);

maptype:

LOAD * INLINE [

maptype

roadmap

mobile

satellite

terrain

hybrid

];

Re: implement google map

jpenuliar — Mon, 22 Jun 2015 15:01:33 GMT

You might have noticed that the script I forwarded to you is a Resident Load from your Original Table named "Sheet1" which you loaded from excel file. try to add the script after your original Load from Excel script.

LOAD [Lease No],
Category,
Manufacturer,
Model,
Technologie,
[Serial Number],
[Selling price],
[Payment (Asset) p. m.],
[Location of installation],
Leasinggeber,
[Lease start date],
[Scheduled lease end date],
[Actual lease end]
FROM
[Testdatenbank 15.06.2015.xlsx]
(ooxml, embedded labels);

Re: implement google map

jpenuliar — Mon, 22 Jun 2015 15:54:05 GMT

Which table is your [Location of installation] located?

I don't think it is in table "Data", try to check in your Table Viewer, most likely, it is in "Sheet1"

Re: implement google map

thanhphongle — Wed, 24 Jun 2015 09:46:29 GMT

it is located in Sheet1.

I changed the resident to Sheet1

STANDORT:

Load Distinct

[Location of installation]

Resident Sheet1;

now it returns me several errors

Table not found

STANDORT:

Load Distinct

[Location of installation]

Resident Sheet1

Script line error:

for i=0 to

Script line error:

next i

Re: implement google map

jpenuliar — Thu, 25 Jun 2015 07:44:37 GMT

can you attach your application?

Re: implement google map

thanhphongle — Fri, 26 Jun 2015 07:57:52 GMT

qvw is attached

Re: implement google map

jpenuliar — Fri, 26 Jun 2015 16:32:31 GMT

attached

Re: implement google map

thanhphongle — Mon, 29 Jun 2015 07:40:57 GMT

i think you attached the wrong file