Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sachinnataraj
Contributor III
Contributor III

Association of maps with postal code

Dear Michael Tarallo,

mto


I am preety new to Qlik sense. Could you please help me in creating a map with postal code (2 digit) for whole Europe.


I saw in few discussions that you have provided KML file for US. Do you have the same for Europe?


I am confused as all countries have same 2 digit postal code.


eg: ES08 & DE08 ,,,,,

Thank you in advance

Sachin

9 Replies
YoussefBelloum
Champion
Champion

Hi,

What is the data structure of your geographic table ?

what do you mean by "a map with a postal code with 2 digits" ?

Can you share with us the expected output ?

Thanks

Patric_Nordstrom
Employee
Employee

Hi Sachin,

There are postal code areas included in Qlik GeoAnalytics. See which countries here:

Qlik GeoAnalytics Coverage

As of March 2018: Argentina, Australia, Canada, Denmark, Ecuador, Finland, France, Germany, Iceland, Liechtenstein, Mexico, Netherlands, Norway, Spain, Sweden, Switzerland, United Kingdom, United States, Uruguay

The postal code system varies country by country in Europe, but many use a digit system. In many cases you reference 2 digit postal by a location id expression like this:

=left(zip,2) & ',' & countrycode & ':XPC'

Thanks,

Patric

alex_robert
Contributor
Contributor

Hi Patric,

I have been creating Postal Code Areas using the below expression:

=left(zip,2) & ',' & countrycode & ':XPC'

This generally works, however I have an issue with UK postal codes due to the Postal areas being 1 or 2 letters

  1. e.g.

B = Birmingham

BS = Bristol

BD = Bradford.

This causes inconsistences in the Area Layer results.

Can you suggest a solution?

I see in the ‘GlobalDrillDown_Example‘  it is possible to achieve  but I have been unable to replicate

Thank you,

Patric_Nordstrom
Employee
Employee

Hi Alex,

Yes UK postal codes follows an own system, here's a good summary:

Postcodes in the United Kingdom - Wikipedia

QGA has support for "Area", "District" and "Sector" postal code areas for GB except Northern Ireland.

One way to parse is (given the the postcode is proper formatted with a space in the right place):

subfield(Postcode,' ',1) & ' ' & left(subfield(Postcode,' ',2),1) AS sector,
subfield(Postcode,' ',1) AS district,
If(IsNum(Mid(Postcode,2,1)),Left(Postcode,1),Left(Postcode,2)) as area

Thanks,

Patric

alex_robert
Contributor
Contributor

Thanks Patric – I have applied this to a drill down dimension and it works perfectly with an area layer.

One more (hopefully final) question:

I am working with multiple countries.

How would I apply country specific drill down in the same Master Item.

i.e. different rules apply depending on the country selected.

e.g.

if I select

UK –drill down as above (Area – District – Sector)

but if I Select

US - drill down by State, County, ZIP, etc.

The same for, Germany, France, etc.

Thank you again,

Alex

Patric_Nordstrom
Employee
Employee

Hi Alex,

In that case you need to write a more complex expression for the drill down group as the  dimension and the location expression Location ID, Here's an example:

=

if(count(distinct total cc)>1,cc) &  // Country level

if(count(distinct total cc)=1 and count(distinct total adm1)>1,adm1 & ',' & cc & ':AADM1') & // Adm1 level

if(count(distinct total adm1)=1 and count(distinct total adm2)>1 and cc<>'DE',adm2 & ',' & cc & ':AADM2')  & // Adm2 level except DE

if(count(distinct total adm1)=1 and count(distinct total adm2)>1 and cc='DE',de_adm2) & // Adm2 level  DE

if(count(distinct total adm2)=1 and cc<>'IT' and cc<>'DE',Name & ',' & cc & ':XPC')  & // XPC level except IT and DE

if(count(distinct total adm2)=1 and cc='IT',it_a3)  & // Adm3 level  IT

if(count(distinct total adm2)=1 and cc='DE',de_zip)  // XPC level  DE

Thanks,

Patric

alex_robert
Contributor
Contributor

Thank you, Patric. Works well.

ersgersg
Contributor III
Contributor III

Hi Patric

The subfiled is not supported in QlikSense datamanager

do you have the equivalent function ?

Thanks

Eric

Patric_Nordstrom
Employee
Employee