topic Re: Association of maps with postal code in App Development

Association of maps with postal code

Anonymous — Tue, 06 Mar 2018 14:53:16 GMT

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

Re: Association of maps with postal code

YoussefBelloum — Tue, 06 Mar 2018 15:15:53 GMT

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

Re: Association of maps with postal code

Patric_Nordstrom — Wed, 07 Mar 2018 07:42:13 GMT

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

Re: Association of maps with postal code

alex_robert — Thu, 26 Apr 2018 14:47:16 GMT

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

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,

Re: Association of maps with postal code

Patric_Nordstrom — Sun, 06 May 2018 15:18:11 GMT

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

Re: Association of maps with postal code

alex_robert — Wed, 09 May 2018 09:58:26 GMT

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

Re: Association of maps with postal code

Patric_Nordstrom — Fri, 11 May 2018 09:37:11 GMT

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

Re: Association of maps with postal code

alex_robert — Mon, 14 May 2018 13:52:18 GMT

Thank you, Patric. Works well.

Re: Association of maps with postal code

Anonymous — Wed, 04 Jul 2018 20:08:01 GMT

Hi Patric

The subfiled is not supported in QlikSense datamanager

do you have the equivalent function ?

Thanks

Eric

Re: Association of maps with postal code

Patric_Nordstrom — Mon, 09 Jul 2018 10:48:42 GMT

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