ZBUSAREA_1.1.3

Back to C17072

Valid entries

Dependent fields

• XBUSLOCUC
• XBUSLOCGR

Depend upon fields

• BUSEMPCITY
• BUSEMPPLOC

Additional information

This algorithm cleans up the data provided by a graduate responding to the Graduate Outcomes survey question "What was the town, city or area in which your business or the main business you were working for, was based during census week?" (BUSEMPCITY). It uses the latest November version of the ONS Postcode Directory (ONSPD) data, validating against the labels for Domicile, Local Authority and Region.

ZEMPAREA contains a valid UK county/unitary authority code; UK government office region code or NOTK.

Contains OS data, © Crown copyright and database right 2020
Contains Royal Mail data, © Royal Mail copyright and database right 2020
Source: Office for National Statistics licensed under the Open Government Licence v.3.0

Geographical mappings for Northern Ireland are based upon Crown Copyright and are reproduced with the permission of Land Property Services under delegated authority from the Keeper of Public Records, Crown copyright and database right 2020. NIMA MOU577.4

Technical Specification

The algorithm uses the HESA Data Management table D_postcode which is created from the ONS Postcode Directory (ONSPD) and restricted to the November YYYY update (postcode mapping is valid at this date), where YYYY represents the year following the academic year of collection. For graduate outcomes 2017/18, the November 2019 ONSPD data is used. The table includes the following fields: Full Postcode (PostCode, VARCHAR(8)), Outward Postcode (OutwardPostcode, VARCHAR(4)), Area Postcode (AreaCode, VARCHAR(2)), County/Region/Unitary Authority/Local Government District code (DomicileCode, VARCHAR(4)) and Government Office Region code (RegionCode, VARCHAR(2)) and labels for Domicile (Domicile, VARCHAR(255)), Local Authority (LocalAuthority, VARCHAR(255)) and Region (Region, VARCHAR(255)). Some of the larger cities which are not found in the domicile labels in their own right have been independently mapped. e.g. Bristol which has a local authority label of 'Bristol, City of'.

1. Convert string from BUSEMPCITY to upper case

2. Trim white space from beginning and end of string

3. Remove any bracketed information from the string

4. Replace special characters (see list below*) with a single space

5. Remove multiple spaces within, retaining only one

6. Map empty string to NULL

7. Carry out the following steps, taking a top down approach stopping when criteria is satisfied:

• if the string contains the words** 'LONDON COLNEY' and BUSEMPPLOC is 01, return C919
• if the string contains the word** 'LONDONDERRY' and BUSEMPPLOC is 04, return XG
• if the string contains the word** 'LONDON' and BUSEMPPLOC is 01, return C329
• if the string contains the word** 'BRIGHTON' and BUSEMPPLOC is 01, return C846
• if the string contains the word** 'BRISTOL' and BUSEMPPLOC is 01, return C801
• if the string contains the word** 'DURHAM' and BUSEMPPLOC is 01, return C840
• if the string contains the words** 'PORT GLASGOW' and BUSEMPPLOC is 02, return C280
• if the string contains the word** 'GLASGOW' and BUSEMPPLOC is 02, return C260
• if the string contains the word** 'EDINBURGH' and BUSEMPPLOC is 02, return C230
• if the string contains the word** 'DUNDEE' and BUSEMPPLOC is 02, return C180
• if the string contains the word** 'ABERDEEN' and BUSEMPPLOC is 02, return C100
• if D_PostCode.Domicile** matches the string and corresponds to a unique DomicileCode (excluding country codes XF, XG, XH, XI, Z), return DomicileCode
• if D_PostCode.LocalAuthority** matches the string and corresponds to a unique DomicileCode (excluding country codes XF, XG, XH, XI, Z), return DomicileCode
• if D_PostCode.Region** matches the string and corresponds to a unique DomicileCode (excluding country codes XF, XG, XH, XI, Z), return RegionCode
• if D_PostCode.Domicile** matches the string and corresponds to multiple DomicileCode (excluding country codes XF, XG, XH, XI, Z), but corresponds to a unique RegionCode, return RegionCode
• if D_PostCode.LocalAuthority** matches the string and corresponds to multiple DomicileCode (excluding country codes XF, XG, XH, XI, Z), but corresponds to a unique RegionCode, return RegionCode
• if the string contains the word 'ENGLAND' and BUSEMPPLOC is 01, return XF
• if the string contains the word 'WALES' and BUSEMPPLOC is 03, return XI
• if the string contains the word 'SCOTLAND' and BUSEMPPLOC is 02, return XH
• if the string contains the word 'NORTHERN IRELAND' and BUSEMPPLOC is 04, return XG
• if the string contains the words 'NEW JERSEY' return NOTK
• if the string contains the word 'JERSEY' or 'GUERNSEY' return XL
• if the string the string contains the word 'ISLE OF MAN' return IM
• else return NOTK.

**Take the upper case version of the string from D_PostCode. The individual words must match within the string e.g. "York" is contained within "Yorkshire", but does not match the entire string.

*Special characters include: ! " # $ % ' ( ) * + , \ - . / : ; < = > ? @ [ ] ^ _ ` { | } ~

Revision history

Contact Liaison by email or on +44 (0)1242 388 531.