Association of maps with postal code

[Anonymous]

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

[YoussefBelloum]

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]

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]

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]

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]

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]

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]

Thank you, Patric. Works well.

[Anonymous]

Hi Patric

The subfiled is not supported in QlikSense datamanager

do you have the equivalent function ?

Thanks

Eric

[Patric_Nordstrom]

Hi Eric,

Use the "Split" tab in the dimension:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/splitting-a-field.htm

Thanks,

Patric