ZEMPCOUNTRY_1.1.2
Field description | Field abbreviation | Field version | Field length | Field type |
---|---|---|---|---|
Country of employment | ZEMPCOUNTRY | 1.1.2 | 4 | Char |
Valid entries
Dependent fields
- XEMPLOCUC
- XEMPLOCGR
Depend upon fields
- EMPCOUNTRY
- EMPCOUNTRY_OTHER
Additional information
This algorithm cleans up the data provided by a graduate responding to the Graduate Outcomes survey question "In which country is your place of work?", combining data from EMPCOUNTRY and EMPCOUNTRY_OTHER. It uses country labels to validate the string returned.
ZEMPCOUNTRY contains a valid UK or overseas country code or overseas geographic region code or NOTK.
Technical Specification
The algorithm uses the HESA Data Management table D_country which is created from an overseas lookup and restricted to the November YYYY update (country 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: Country Code (CountryCode, VARCHAR(2)), Country Name (Country, NVARCHAR(255)), Alternate Country Name (CountryAlternateName, NVARCHAR(255)), Alternate Country Name (version 2) (DW_CountryAlternateName_GO, NVARCHAR(255)), Geographic Region Code (GeographicGroupCode, NVARCHAR(4)), Region Name (GeographicGroup, NVARCHAR(255)) and Alternate Region Name (DW_GeographicGroupAlternateName_GO, NVARCHAR(255)).
1. Convert the string from EMPCOUNTRY_OTHER to upper case
2. Trim white space from beginning and end of string
3. Remove any bracketed information from the string
4. Replace "&" with " and "
5. Replace special characters (see list below*) with a single space
6. Remove multiple spaces within, retaining only one
7. Map empty string to NULL
8. Carry out the following steps, taking a top down approach stopping when criteria is satisfied:
- if 'NORTHERN IRELAND' is contained in string, return XG
- if 'NORTHER IRELAND' is contained in string, return XG
- if D_Country.CountryAlternateName** is contained in string and corresponds to a unique CountryCode, return CountryCode
- if D_Country.DW_CountryAlternateName_GO** is contained in string and corresponds to a unique CountryCode, return CountryCode
- if D_Country.DW_GeographicGroupAlternateName_GO** is contained in string and corresponds to a unique CountryCode, return GeographicGroupCode
- else return NOTK.
9. Where EMPCOUNTRY is not NULL and not equal to OTHER or the empty string, copy the values from EMPCOUNTRY into ZEMPCOUNTRY (note: due to routing, only NOTK values should be overwritten with values from EMPCOUNTRY)
*Special characters include: ! " # $ % ' ( ) * + , \ - . / : ; < = > ? @ [ ] ^ _ ` { | } ~
**Take the upper case version of the string D_Country. The individual words must match within the string e.g. "UK" is contained within "Ukraine", but does not match the entire string.
Revision history
Contact Liaison by email or on +44 (0)1242 388 531.