ZSTUCOUNTRY_1.1.2
Field description | Field abbreviation | Field version | Field length | Field type |
---|---|---|---|---|
Country of Provider of further study | ZSTUCOUNTRY | 1.1.2 | 4 | Char |
Valid entries
Dependent fields
- XSTULOCUC
- XSTULOCGR
Depend upon fields
- STUCOUNTRY
- STUCOUNTRY_OTHER
Additional information
This algorithm cleans up the data provided by a graduate responding to the Graduate Outcomes survey question "In which country were you studying?", combining data from STUCOUNTRY and STUCOUNTRY_OTHER. It uses the country codes from CountryCode, consistent with those returned in STUCOUNTRY. In addition, it also uses valid geographic region codes. Where the string in D4Other looks up to one of the values in the field CountryAlternateName or CountryAlternateName2 which corresponds to a unique CountryCode take this value; if the string in D4Other looks up to one of the values in GeographicGroupAlternateName which corresponds to a unique GeographicGroupCode, take this value; if STUCOUNTRY contains a value, other than 'OTHER' NULL or empty string, take this; otherwise set to 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 D4Other 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 STUCOUNTRY is not NULL and not equal to OTHER or the empty string, copy the values from STUCOUNTRY into ZSTUCOUNTRY (note: due to routing, only NOTK values should be overwritten with values from STUCOUNTRY)
*Special characters include: ! " # $ % ' ( ) * + , \ - . / : ; < = > ? @ [ ] ^ _ ` { | } ~
**Take the upper case version of the string [Exploitation].[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.