topic Re: Map issue in QlikView

Map issue

Wed, 25 Nov 2020 16:16:04 GMT

Hi, I am using following code for my map and there is a problem with the Georgia because it is displaying the Georgia state in US. How can i change that? please help. Thanks

DISTRICT_LINK_MAP:

LOAD

Distinct (mid(COUNTRY_NAME,1,(Len(COUNTRY_NAME)-3))) AS DISTRICT_NAME

FROM

(qvd) Where COUNTRY_NAME<>'SRI LANKA';

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

for i=0 to $(noRows)

//let a = peek('CONTRACT',$(i),'Customer_Master');

let b = peek('DISTRICT_NAME',$(i),'DISTRICT_LINK_MAP');

// let c = peek('Quantity',$(i),'CustomerTable');

GeocodeResponse:

LOAD

status,

//'$(a)' as CustomerName,

'$(b)' as DISTRICT_NAME,

//'$(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;

max_zoom_level = 5; //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 = '640';//640

map_size_y = '400';//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

];

set hideprefix = 'maptype';

Re: Map issue

Tue, 28 Nov 2017 04:16:49 GMT

I want to locate the country "Georgia".

Re: Map issue

woshua5550 — Tue, 28 Nov 2017 07:35:45 GMT

can you provide this qvd file ?

"COUNTRY_MF.qvd"

Re: Map issue

Tue, 28 Nov 2017 08:18:12 GMT

Hi Dave, this is the data set in that QVD file. i shared it like this becuase i cant find a place to attach the file. Sorry for the inconvenience. thanks

COUNTRY_CODE	COUNTRY_NAME
ABU	United Arab Emirates;AE
ADE	Australia;AU
AGH	Turkmenistan;TM
AJM	United Arab Emirates;AE
AKT	Kazakhstan;KZ
ALG	Algeria;DZ
ALM	Kazakhstan;KZ
ALX	Egypt;EG
AMB	Turkey;TR
AMS	Netherlands;NL
ANK	Turkey;TR
ANT	Belgium;BE
APA	Nigeria;NG
API	Samoa;WS
AQA	Jordan;JO
ARM	Armania;AM
AST	Russian Federation;RU
ATH	Greece;GR
ATY	Kazakhstan;KZ
AUC	New Zealand;NZ
BAB	Iran, Islamic Republic of;IR
BAM	Mali;ML
BAN	Iran, Islamic Republic of;IR
BAT	Georgia;GE
BED	United States;US
BEI	Benin;BJ
BEJ	Algeria;DZ
BEL	Serbia;RS
BEN	Libya;LY
BER	Lebanon;LB
BHA	Bahrain;BH
BIR	United Kingdom;GB
BIS	Kyrgyzstan;KG
BIT	Iran, Islamic Republic of;IR
BKU	Azerbaijan;AZ
BLR	Russian Federation;RU
BNK	Thailand;TH
BSB	Australia;AU
BSP	Libya;LY
BSR	Iraq;IQ
BTQ	Iran, Islamic Republic of;IR
BUL	Bulgaria;BG
BUS	South Korea;KR
CAR	Australia;AU
CEL	Czech Republic;CZ
CFT	United States;US
CHA	Iran, Islamic Republic of;IR
CHL	Chile;CL
CHN	India;IN
CHT	Bangladesh;BD
CIC	United States;US
CLL	Peru;PE
CLS	Chile;CL
COC	India;IN
CON	Guinea;GN
COP	Denmark;DK
COT	Benin;BJ
CPT	South Africa;ZA
DAM	Saudi Arabia;SA
DET	United States;US
DKR	Senegal;SN
DLC	China;CN
DMA	Saudi Arabia;SA
DOH	Qatar;QA
DUB	United Arab Emirates;AE
DUS	Tajikistan;TJ
ELK	Libya;LY
FAM	Turkey;TR
FAN	China;CN
FEL	United Kingdom;GB
FLT	France;FR
FRE	Freetown;SL
FRK	Germany;DE
FRM	Australia;AU
GEN	Italy;IT
GEV	Switzerland;CH
GNZ	China;CN
GOT	Sweden;SE
GRF	Australia;AU
GYD	Poland;PL
HAM	Germany;DE
HEL	Finland;FI
HNG	China;CN
HNL	Hawaii;HI
HOB	Australia;AU
HON	Hong Kong;HK
HOU	United States;US
HPG	Vietnam;VN
HUA	China;CN
ILI	United States;US
ILL	Ukraine;UA
ILLMD	Moldova;MD
INC	South Korea;KR
IQU	Chile;CL
ISK	Turkey;TR
JEB	United Arab Emirates;AE
JED	Saudi Arabia;SA
JIJ	China;CN
KAN	Kanagawa;JP
KAR	Kazakhstan;KZ
KEE	Taiwan;TW
KHA	Ukraine;UA
KIS	Iran, Islamic Republic of;IR
KLA	Malaysia;MY
KLJ	Lithuania;LT
KLM	Malaysia;MY
KOA	Taiwan;TW
KOS	Kosovo;AL
KOT	Russian Federation;RU
KOW	Hong Kong;HK
KRC	Pakistan;PK
KSN	Moldova;MD
KUW	Kuwait;KW
KWI	Kuwait;KW
KYV	Ukraine;UA
LAG	Nigeria;NE
LAP	Bolivia;BO
LAT	Syrian Arab Republic;SY
LBH	United States;US
LEH	France;FR
LGP	United Kingdom;GB
LHR	United Kingdom;GB
LIA	China;CN
LIS	Portugal;PT
LOB	Angola;AO
LOM	Togo;TG
LON	United Kingdom;GB
LOS	United States;US
LPZ	Germany;DE
LUA	Angola;AO
MAC	Macau;MO
MAL	Maldives;MV
MAN	United Kingdom;GB
MEL	Australia;AU
MEN	Czech Republic;CZ
MER	Turkey;TR
MIA	United States;US
MIS	Libya;LY
MLN	Italy;IT
MLT	Malta;MT
MLW	Liberia;LR
MNL	Philippines;PH
MOG	Somalia;SO
MOL	Moldova;MD
MOM	Kenya;KE
MON	Canada;CA
MUM	India;IN
MUN	India;IN
NAG	Japan;JP
NAK	Azerbaijan;AZ
NAM	Angola;AO
NAN	China;CN
NAR	Japan;JP
NEW	United States;US
NIC	Cyprus;CY
NRK	United States;US
NVR	Russian Federation;RU
OAK	United States;US
ODE	Ukraine;UA
OKI	Japan;JP
OSK	Japan;JP
OSL	Norway;NO
PAP	French Polynesia;PF
PAR	France;FR
PNG	Malaysia;MY
POT	Georgia;GE
POZ	Poland;PO
PRG	Czech Republic;CZ
PRN	Kosovo;XK
PTH	Australia;AU
PUK	United Kingdom;UK
PUN	Chile;CL
QES	Iran, Islamic Republic of;IR
QNG	China;CN
REY	Iceland;IS
RIG	Latvia;LV
RIY	Saudi Arabia;SA
ROT	Netherlands;NL
RTB	Germany;DE
RUN	France;FR
SAE	Egypt;EG
SAI	Egypt;EG
SAM	Uzbekistan;UZ
SAW	Egypt;EG
SHA	China;CN
SHY	Kazakhstan;KZ
SHZ	China;CN
SIH	Cambodia;KH
SIN	Singapore;SG
SOU	United Kingdom;GB
STP	Russian Federation;RU
SUV	Fiji;FJ
SWN	Sweden;SE
SYD	Australia;AU
TAI	Taiwan;TW
TAR	Kiribati;KI
TAS	Uzbekistan;UZ
THI	Taiwan;TW
TIL	United Kingdom;GB
TIN	Nigeria;NG
TLL	Estonia;EE
TMA	Ghana;GH
TNJ	China;CN
TOB	Libya;LY
TOK	Japan;JP
TOR	Canada;CA
TRB	Turkey;TR
TRI	Italy;IT
TRP	Libya;LY
TSL	Greece;GR
TUN	Tunisia;TN
TUT	India;IN
ULA	Mongolia;MN
UMM	Iraq;IQ
USA	United States;US
UZB	Uzbekistan;UZ
VAL	Chile;CL
VAN	Canada;CA
VIC	Seychelles;SC
VIE	Austria;AT
VLC	Spain;ES
VLN	Spain;ES
WAP	Poland;PL
WAW	Poland;PL
YAN	Burma;MM
YEM	Yemen;YE
YOK	Japan;JP
YRV	Armenia;AM
ZHU	China;CN

Re: Map issue

avkeep01 — Tue, 28 Nov 2017 08:55:45 GMT

Hi, maybe you could try the app I created on loading geolocations from Google. Maybe you should edit your URL, just look at my example if you want.

GOOGLE API GEO location loader

Re: Map issue

woshua5550 — Tue, 28 Nov 2017 09:14:11 GMT

in my opinion

the reason which caused this issue is that there are two "Georgia" in xml data, one is the country Georgia which you want ,another one is the State Georgia in USA which you don't need

you must let b = 'Georgia;GE' rather than 'Georgia' to get the right latotude and longitude

so you can get rid of mid function while loading qvd data like this

DISTRICT_LINK_MAP:

LOAD

Distinct COUNTRY_NAME AS DISTRICT_NAME

FROM

(qvd) Where COUNTRY_NAME<>'SRI LANKA';

if you really need your original DISTRICT_NAME you can create a new field

Re: Map issue

Tue, 28 Nov 2017 09:49:39 GMT

Hi Dave, what do you mean by create a new field?

Re: Map issue

woshua5550 — Tue, 28 Nov 2017 10:08:09 GMT

like this

DISTRICT_LINK_MAP:

LOAD Distinct

COUNTRY_NAME AS vDISTRICT_NAME,

Mid(COUNTRY_NAME,1,(Len(COUNTRY_NAME)-3)) AS DISTRICT_NAME

FROM

(qvd) Where COUNTRY_NAME<>'SRI LANKA';

//change variable 'b'

Let b = peek('vDISTRICT_NAME',$(i),'DISTRICT_LINK_MAP');