Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE

Mapping Postal codes

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Patric_Nordstrom
Employee
Employee

Mapping Postal codes

Last Update:

Apr 2, 2021 4:34:15 AM

Updated By:

Patric_Nordstrom

Created date:

May 30, 2019 4:28:15 PM

Attachments

Mapping data based on postal code areas or center points is a common task. However postal codes is not a one global standard and there is lot of variation between countries in format and structure that makes the task challenging.

Also the availability of postal code areas varies, many countries are included in the Qlik Location database, but for others postal code areas need to be purchased separately.

Workflow

Here's the high level workflow, the bulk is data preparation before rendering

1. Prepare the data
- Make the postal codes unique
- Pad with zeroes
- Some countries are special
- Check coverage
- Build overview layers when possible

This app will show how to map postal code areas for five European countries: France, Great Britain, Germany and Italy. The indata is not clean and contains errors like in most uses cases.

Prepare the data

Make the postal codes unique

When mapping postal codes for several countries the first step is to make the postal code unique. The easiest way to do this is to add country code prefixed with a comma after the postal code.

=Postal & ',' & CountryCode as zip

Pad with zeroes

In many cases Excel drops leading zeroes in numerical postal codes. I can see that when looking at the max and min lengths. Note that not all postal codes are numeric though and UK is special so we fix that later. Pad with leading zeroes for France, Germany and Italy like this:

=Num(PostalCode,'00000')
 
tablezip.png

 

Great Britain is special

The postcodes are alphanumeric, and are variable in length: ranging from six to eight characters (including a space) long. Each post code is divided into two parts separated by a single space. More info at Wikipedia. Here's the three levels comparable to 2,3 and 4 digit numeric postal codes:

Area: 1 or 2 letters
District: 1 or 2 digits or a digit followed by a letter
Sector: 1 digit
 
Can be parsed like this if the the space is in the right place:
=If(IsNum(Mid(zip,2,1)),Left(zip,1),Left(zip,2)) & ',' & cc // Area
=subfield(zip,' ',1) & ',' & cc // District
=subfield(zip,' ',1) & ' ' & left(subfield(zip,' ',2),1) & ',' & cc // Sector
 
Check coverage
By checking the table at Qlik GeoAnalytics Coverage I can tell that there is built support for postal code areas for most of the countries I am interested. However:
(1) Postal code areas are missing for Italy.
(2) France doesn't support 2,3,4 level postal code areas.
(3) Northern Ireland is not included in the areas for Great Britain.
 
coverage.png

 

 
Truncate for 2 and 3 digit postal code levels
Germany and Switzerland are easy, just truncate the zip code and add the country code. Add the following before the load:
 
load *,
left(zip,2) & ',' & cc as zip2,
left(zip,3) & ',' & cc as zip3;
 
France, IntersectsMost
For France we will use administrative areas level 1 and level 2 as replacement of the non-existing postal code areas of 2 and 3 digit.
 
Using the QGA connector operation IntersectMosts will construct the hierarchy between postal code and admin boundaries:  Adm1>Adm2>Zip. For details see the load script.
 
intersectsmost.png

 

Italy, Within
For Italy we will also use admin 1 and 2, and use a point layer for the postal code level.
 
Using the QGA connector operation Within we can link postal code center points to the administrative boundary level 2: Adm1>Adm2>Zip. For details see the load script.
 
within.png

 

Create map view
After that it's easy to put together a map view.
1. Create a drill down group Country > zip2 > zip3 > zip
2. Add map chart and a area layer, drill down group as dimension, color by Sum(Sales)
3. Add a point layer only visible on level "zip", add a set expression for location: only({<cc={'IT'}>}zip)
 
zip3.png
See the attached app and data for more details.
Tags (1)
Comments
hectorgarcia
Partner - Creator III
Partner - Creator III

@Ana_Yakushi  any update of the new release ???

0 Likes
Ana_Yakushi
Employee
Employee

Hello @hectorgarcia, the new version of the location database was released on Monday 17th of October. The Colombian postal code areas are now available. 

0 Likes
Antoine04
Partner - Creator II
Partner - Creator II

Hi,

I am working on a subjet and some help would be appreciated.

My DATA are simple : it's just 100 customers with their postal codes.

I want to use the TravelAreas operation to have a travelarea from this 100 points.

So my questions are :

1. Can I use the operation Travelareas directly from my postal codes? Or Travelareas are doable only from coordinates ?

2. How can I get the coordinates from my 100 postal codes ? By using the operation Load ?

Thank you by advance

Best Regards,

0 Likes
Ana_Yakushi
Employee
Employee

Hello @Antoine04,

You don't need to have the coordinates.

If you are using the GeoAnalytics connector,  you just need to select "Location Named Point" as the Geometry Type. Don't forget to include the Country code as part of the postal code.

Ana_Yakushi_0-1683743128403.png

 

If you are using GeoOperations then your can also use the postal code directly:

MyZipcodes:
load * inline "
xpcid; xpc
1;43365,SE
2;43131,SE
3;43540,SE
" (delimiter is ';');

Load * Extension
GeoOperations.ScriptEval('TravelAreas(costValue="10",costUnit="minutes")
DATASOURCE zipcodes INTABLE keyField="xpc", namedPointField="xpc"', MyZipcodes {xpc});

 

0 Likes
Version history
Last update:
‎2021-04-02 04:34 AM
Updated by: