topic Re: load postal code into map in QlikView

load postal code into map

Anonymous — Sat, 21 Mar 2015 01:07:45 GMT

I have each store's postal code (NO longitude/latitude) with sales, want to load them into map to show where the best locations are.

What's the easiest way to implement them on a map? Thanks,

Re: load postal code into map

prieper — Sat, 21 Mar 2015 09:01:01 GMT

Which country?

Think that you will need a kind of mapping table to link a postal code to geo-data in order to present in e.g. Google Maps.

Have you googled to:

http://download.geonames.org/export/zip/

already?

Peter

Re: load postal code into map

Anonymous — Sat, 21 Mar 2015 11:24:09 GMT

Sorry for not being clear, I need USA and Canada.

Re: load postal code into map

fred_s — Sat, 21 Mar 2015 14:01:33 GMT

Hi Joe,

To get you started.. see attachment

I'll have a closer look.

Grtz Fred

Re: load postal code into map

fred_s — Sat, 21 Mar 2015 14:54:37 GMT

I believe this should get you going..

A couple of things to remember/check..

My script variables are western Europe settings (first tab in QV script), means things can be (/will be) different in your country (seperators, monthnames etc)

The Postalcode I use to link locations to customers/stores is something like US-80302 or CA-M5H.

It's countrycode (US/CA) plus for US the numeric part, for Canada the first 3 characters.

Countrycode and Postal are concatenated in the script, so you can use address-data like I did in my script:

Debno, Name, Address, Postcode, City, Country

5, Google On, 12th Floor 111 Richmond Street West, M5H 2G4, Toronto, CA

6, Google Ch, 20 West Kinzie St., IL 60654, Chicago, US

You can use the slider to zoom.

You can't drag the map.

Have fun 🙂

Btw If your question is answered, please mark it as answered..

Re: load postal code into map

Anonymous — Wed, 25 Mar 2015 01:16:37 GMT

Hello, Fred, Thanks for your work and explanation.

Unfortunately I am using Personal Edition,could not open the file. Is that anyway to walk around? thanks.

Re: load postal code into map

fred_s — Wed, 25 Mar 2015 08:38:52 GMT

Step by step?

1. First lets see if you can get a working Google maps in your PE.

In the folder C:\Program Files\QlikView\Examples\Documents

open the document "Whats New in QlikView11.qvw" and go to tab Customer.

Or have a look in the other example files.

2. Save my file I've posted earlier, US-CA.txt

These are the Postcodes you're going to use to link to your postal codes.

Re: load postal code into map

Anonymous — Sat, 11 Apr 2015 13:15:27 GMT

Hello, Fred, Just back from a off site business trip. Sorry for late reply.

I have checked what's new sample, the google map works there. Your txt file has been saved well.

what's the next step? Thanks.

Re: load postal code into map

fred_s — Sat, 11 Apr 2015 19:38:14 GMT

The easiest way is to share a part of your data, a couple of your stores, some in the states and some Canadians.

I will create your script and you can copy this script in your Qlikview file later.

Re: load postal code into map

Anonymous — Fri, 17 Apr 2015 00:42:44 GMT

Hello, Fred, please find the attached excel file, thanks.

Re: load postal code into map

fred_s — Fri, 17 Apr 2015 23:07:59 GMT

Hi Joe,

1. In the folder C:\Program Files\QlikView\Examples\Documents

open the document "Whats New in QlikView11.qvw" and go to tab Customer.

2. Rightclick the map and choose "Copy to clipboard" and "Object"

3. Create a new Qlikview file

4. Rightclick on your worksheet and "Paste sheet object"

5. Save your Qlikview file

6. Make sure the US-CA.TXT is in the same folder

7. Now choose File - Edit script

and paste the following at the last line

// ******************************************************************

stores:

Load [store number],

[Store Name],

[Postal Code] as Postal_org,

State as State_org,

Sales,

Country,

upper(Country) & '-' & Pcode as Postalcode

;

Load *,

if(Country = 'CA', left([Postal Code], 3), keepchar([Postal Code], '0123456789')) as Pcode,

num#(Salesorg) as Sales

;

LOAD [store number],

[Store Name],

[Postal Code],

State,

Sales as Salesorg,

Country

FROM

[Store Sample.xls]

(biff, embedded labels, table is Sheet1$);

postalcodes:

left keep

Load *,

upper(Isocountry) & '-' & Pc as Postalcode,

num#(replace(Xlat,'.',',')) as latitude ,

num#(replace(Xlong,'.',',')) as longitude

;

LOAD @1 as Isocountry,

@2 as Pc,

@3 as City,

@4 as State,

@5 as Stateshort,

@6,

@7,

// @8,

// @9,

@10 as Xlat,

@11 as Xlong,

@12

FROM

[US-CA.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

// Google Maps in QlikView

// V0.9 - October 1st 2008 © Copyright QlikTech International AB 2008 / AES

// Google Maps Key

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

map_size_y= '600';

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

];

// ******************************************************************

8. Rightclick your map > Properties > Colors

Bottom left there's a Dynamic Image, click ... to edit the formula

Delete the old formula and copy and paste the following:

='http://maps.googleapis.com/maps/api/staticmap?center='

num(var_mid_lat, '##############', '.', ',' )

','

num(var_mid_long, '##############', '.', ',' )

'&zoom=$(var_zoom)'

'&maptype='&var_maptype

'&size='&map_size_x&'x'&map_size_y

'&sensor=false'

9. Save your file (Ctrl-S) and Reload the data (Ctrl-R)

10. Rightclick the map > Properties

- Dimensions > Remove Customer and add City

- Expressions, do not change the first and second

. First extension formula: change Longitude to longitude (lowercase)

. Second extension formula: change Latitude to latitude (lowercase)

. Third extension formula needs to be: =if(sum(Sales) > 0, sum(Sales), null() )

>> Text as Popup (at the bottom) unchecked

. Fourth extension formula needs to be: =City & ' (' & [Store Name] & ') ' & money(Sum(Sales))

>> Enable (at the top)

>> Text as Popup (at the bottom) checked

11. Save (Ctrl-S) and Reload (Ctrl-R) your file

Questions or problems?

Attach your QVW and I'll have a look..

Grtz Fred

Re: load postal code into map

fred_s — Sun, 19 Apr 2015 08:37:55 GMT

Forgot to mention.. I've added a field Country (CA/US) to your Store sample data, see attachment.

Grtz Fred