Data Format Validation

After the data file is Uploaded and Transposed if selected, the uploaded file is inspected and the data is validated against the Tariff Dataset

Each record is validated:

Where the Tariff Dataset indicates a field has options, the fields in the record are validated against the options described in the Tariff Dataset.

Where the Tariff Dataset indicates a field is open but requires a specific format eg date or LSOA, the validation process will validate the data against the alphanumeric format described in the Tariff Dataset.

Where the Tariff Dataset indicates a field is free formatted the grouper will not perform any validation other than constrain the size of the data accepted as indicated in the Tariff Dataset.

Special Validation

Some fields have special validation characteristics:

LSOA field

The LSOA field requires a valid and current LSOA.  The validation process uses the Ministry of Housing Communities and Local Government's web service to identify valid LSOAs

Leicestershire 006D - this LSOA is valid

Leeds 055C - this LSOA is no longer live

Please note: data files uploaded and validated on one date and then Submitted at a substantially later date, may find that LSOA have been terminated by the time the submission is made and this will cause a Submission Error.

Date fields

The Grouper does not validate the Attendance Date with the month of Submission - that is to say it is quite possible to upload Apr 17 data into the May 17 submission.

If data files are uploaded in xls or xlsx format, date fields are usually automatically formatted as dates.  Microsoft Excel will usually automatically format a cell containing a date as a date format. 

DateFormat The Grouper accepts cells formatted as dates.  If this was Saved As a .csv file, the field would not be valid, dates in csv files must always be formatted 'YYYY-MM-DD' or in this case '2017-04-03' for 3 Apr 2017
     
 DateFormat2  The Grouper will not recognise unformatted date cells in excel that contain date serial numbers.

 

Date Systems

Microsoft Excel suports two different date systems.  These systems are the 1900 date system and the 1904 date system each systems starts its date serail number from either the 1 Jan 1900 or 1 Jan 1904 respectively.  The difference between the two date systems is 1,462 days.  By default, Microsoft Excel for the Macintosh uses the 1904 date system and Microsoft Excel for Windows uses the 1900 date system.   This can lead to problems copying dates between workbooks created where different date systems have been used.  The dates will be shifted by four years.  To correct this problem and understand the issue further, please refer to Differences between the 1900 and the 1904 date system in Excel.

Clinic ID

Clinic IDs are free form and limited to 15 characters, they are not validated against ODS.  However, new Clinic IDs will be added to the Provider's list of My Clinics (see below) and providers must ensure each Clinic ID is edited to include the correct Clinic address.

Clinics with no recorded address will have the default location of the parent provider organisation.

Clinic Address

Clinic ID locations are used to identify the Local Host Commissioner for the Clinic.

Local Host Commissioners are responsible for paying for overseas patients and patients who have not disclosed their residential address.