Student record - Data Futures
HESA Data Platform expression syntax
Version 1.2.0 Produced 2018-07-09
The HESA Data Platform (HDP) system uses expressions to define derived fields and quality rules.
A derived field has a single expression that is evaluated against every record of an entity. A quality rule has an expression to define the population of records that the rule is applicable to, and an expression that determines if the record is valid or invalid.
An expression is a combination of one or more values, operators and functions that evaluate to a single value. A simple expression can be a constant, a field reference or a function. Operators can be used to join two or more simple expressions into a complex expression.
The HDP expression syntax is based on SQL expressions and is divided into the following categories:
- Field Reference Expressions.
- Function Expressions.
- Conditional Expressions.
Field reference expressions
An expression is always evaluated in the context of an entity, so references to fields in that entity are the short name of the field preceded by a dot. Fields from other entities are prefixed with the names of those entities. Field references can reference any descendant or ancestor field, exemplified in the following. In this example the expressions are written in the context of the Student registration entity:
|Field in own entity||References to fields in the same entity are prefixed with a dot.||.REGISTRATIONSTARTDATE||Refers to the Student registration start date for a Student registration.|
|Field in parent entity||References to fields from a parent entity include the name of the parent entity as the prefix.||.Engagement.HIGHESTQUALOE||Refers to the Highest qualification on entry in the Engagement for a Student registration.|
|Field in grandparent entity||References to fields from a grandparent entity include the names of the grandparent and parent entities as prefixes.||.Engagment.Student.BIRTHDTE||Refers to the student Birthdate for the Engagement for a Student registration.|
|Field in child entity||References to fields from a child entity include the name of the child entity as a prefix. Note that because multiple child rows could exist, the reference must be wrapped in an aggregating function, e.g. SUM, COUNT, MAX, etc even if the relationship defines that there is a maximum of only one child.||SUM(.StudentCourseSession.STULOAD)||Results in the sum of the Student load in a student course session for a Student registration.|
|Field in filtered child entity||References to fields from a child entity which has been filtered include the filter criteria in square brackets after the child entity name. Field references in the criteria start from the filtering entity.||COUNT(.CourseRole[.ROLETYPE = '301'].COURSEID)||Returns the count of the number of CourseRoles for a Course where the ROLETYPE is 301.|
|Field in filtered child entity using the base entity||A double dot prefix is used for field references in a filtering expression that refer to a field in the base entity rather than the filtering entity.||On CourseSession: COUNT(.CourseDelivery.CourseSession[.STARTDATE = ..STARTDATE])||Returns the number of course sessions that share the same start date within the same course delivery for any course session.|
Most SQL operators are supported, including:
|+, -, *, /||Arithmetically adds, subtracts, multiplies or divides two numbers together.||3 + 4||Results in 7.|
|+ (concat)||Concatenates two strings together.||'Hello' + 'World'||Results in 'Hello World'.|
|=, <,>, <=, >=, <>||Compares two values for equal to, less than, greater than, less than or equal to, greater than or equal to or not equal to.||.Field1||Results in TRUE if Field1 is less than or equal to Field2.|
|AND, OR||Logically ANDs or ORs the conditions together.||.Field1 = 3 AND .Field2 = 6||Results in TRUE if Field1 is 3 and Field2 is 6.|
|IN||Returns true if a value matches any of the set of values.||.Field1 IN (2, 5, 8)||Results in TRUE if Field1 is 2, 5 or 8.|
|BETWEEN||Returns true if a value is between or equals two values.||.Field BETWEEN 1 AND 10||Results in TRUE if Field1 is greater than or equal to 1 and less than or equal to 10.|
|NOT||Returns the inverse of a Boolean expression.||NOT .Field1 IN (2, 5, 8)||Results in TRUE if Field1 is not 2, 5, or 8.|
|LIKE||Returns true if a string matches a pattern.||.Field1 LIKE '[A-Z][A-Z][0-9][0-9]'||Results in TRUE if Field1 begins with two upper case alphabetic characters followed by two numeric digits.|
|IS NULL, IS NOT NULL||Returns true if a value is null (or is not null).||.Field1 IS NULL||Results in TRUE if Field1 is null.|
Most built-in SQL functions are supported, along with HDP-specific convenience functions, including:
|COUNT, SUM, MIN, MAX||Aggregating functions that return the count, sum, minimum or maximum values from a set of values from a field in a child entity.||SUM(.StudentCourseSession.STULOAD)||Returns the sum of student load for a student registration.|
|DateDiff||Returns the integer count (positive or negative) of the specified date boundaries crossed between a start date and an end date. Date boundaries can be years, months, days, etc.||DateDiff(year, .BIRTHDTE, '20170101')||Returns the numbers of year boundaries crossed between a students birthdate and the 1st of January 2017. If the birthdate was 31st of December 2016, 1 would be returned.|
|DateAdd||Adds or subtracts an integer number of date parts (e.g. months, years) to or from another date.||DateAdd(day, -14, .REGISTRATIONSTARTDATE)||Returns the date 14 days before the registration start date.|
|Age||Calculates the age of a person on a particular date based on their date of birth.||Age(.BIRTHDTE, '20170831')||Returns the age of the student on the 31st of August 2017.|
|ABS||Returns the absolute value of a number, disregarding any negative sign.||ABS(-27) ABS(27)||Returns 27 in both cases.|
Most conditional SQL expressions are supported, including:
|CASE WHEN||Compares an expression to a set of simple expressions to determine a result or evaluates a set of Boolean expressions to determine a result.||CASE .FEEELIG WHEN '01' THEN 'Eligible to pay home fees' WHEN '02' THEN 'Not eligible to pay home fees' WHEN '03' THEN 'Eligibility to pay home fees not assessed' ELSE 'Invalid fee eligibility code!' END||Results in a description of the fee eligibility.|
|IIF||Returns one of two values depending on the result of another logical expression.||IIF(.FEEREGIMEINDICATOR IN ('01','02'), 'Regulated fees', 'Not regulated fees')||Returns text of either regulated fees or not regulated fees depending on the fee regime of the student registration.|
|ISNULL||Replaces NULL with the specified replacement value.||ISNULL(.Field1, 'ABC')||If Field1 is not null, the return is Field1, otherwise the return is 'ABC'.|
Contact Liaison by email or on 01242 211144.