load postal code into map

[Anonymous]

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,

[fred_s]

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

[fred_s]

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

Grtz Fred