ILR Exports Comparison
View contentsThe ILR Exports Comparison report lets you choose two ILR exports and compare them to identify the differences between them.
The intention behind the report is to help providers account for changes between files, such as starters and leavers, which can help to explain things like funding differences in the PFR reconciler or potential issues in the achievement rates.
Parameters
- Years – This filters the available ILR exports to select based on the specified contract years.
- Collection periods – This filters the available ILR exports.
- File status – This filters the available ILR exports based on the status of each export file. By default, only files where the status indicates they were output or the file preparation was finished are included. The other statuses option can be selected to show any other files (e.g. files with errors).
- Only show latest export for each period – When this is set to Yes (the default), only the latest file for each period is included in the list of available files. When set to No, all files for each period will be included, providing they meet the criteria for the above parameters.
- ILR export file 1 and 2 – These let you choose the ILR export files to compare. When the first file is selected, the list of files in the second parameter is limited to exports with the same UKPRN as the first export.
- Field changes to show - This determines the fields which appear on the third table (field changes between file 1 and file 2).
Layout
The report is split into three tables – Files Summary, POT / Aim Count Comparison and Field Changes between File 1 and File 2.
Files Summary
This shows basic information about each file, including the contract year, collection period, submission number, export date, UKPRN, file status and the number of contractors included in each export.
POT / Aim Count Comparison
This shows a comparison of POT counts and aim counts in each file.
- Total POT Count – The total number of POTs in each file.
- Matched POTs – The number of POTs which appear in both files. POTs are matched on trainee ID and POT.
- POTs not in Other File – The number of POTs in each file where the trainee ID and POT do not appear in the other file.
- Total Aim Count – The total number of aims in each file.
- Matched Aims – The number of aims which appear in both files. Aims are matched on trainee ID, POT, learning aim reference and software supplier aim identifier (this is a field generated on the ILR for the purposes of identifying aims).
- Aims not in Other File – The number of aims in each file which do not appear in the other file (based on the criteria for matched aims).
Each of these figures can be clicked to show a drilldown of the relevant POTs and aims.
Field Changes between File 1 and File 2
This shows field-level differences between file 1 and file 2. As of version 1.0.3.1 of the reports library, the table can include every ILR field except learning aim reference and software supplier aim ID, as these are fundamental to matching data between files.
The fields included in this table are determined by the Field changes to show parameter. By default, only fields where there are differences are shown, but you can show all selected ILR fields by setting the Only show ILR columns with changes parameter to No.
If the table does not show at all, this means no changes for the selected fields have been identified.
For each ILR entity (e.g. learners, learning delivery, employment status, etc), the report attempts to match rows between files. If a row matches, the fields on that row are compared to see if the data has changed. If rows cannot be matched between files, all the fields on those rows are counted as a change as they indicate that a row has either been added or removed between files. This means there are three scenarios where data could be counted as a change in this table:
- Where rows are matched between files and data has changed
- Where a row is in file 1 but not in file 2
- Where a row is in file 2 but not in file 1
Note that where a row in an ILR entity is in one file but not the other, only fields in that entity that have a value entered are counted as a change. For example, if a learning delivery row is in file 1 but not file 2 and the actual end date is not entered in file 1, actual end date would not show as having a change for that row (essentially because there is nothing to show for it). Fields on that row where data is entered would show as a change, though.
Each figure on the table can be clicked to show a drilldown of the relevant rows. Each row in the drilldown shows the values from file 1 and file 2 for easy comparison, along with (where relevant / possible) the learner name, POT and aim reference number. The Row ID column is used to distinguish between rows where there may be multiple rows per POT.
Matching Rows
The criteria used to match rows between files is different for each ILR entity:
| ILR entity | Fields used to match rows between files |
|---|---|
| Learners (ILRLEARNERS) | TRAINEEID |
| Learning delivery (ILRLEARNERAIMS) | TRAINEEID, POT, NVQREF and SWSUPAIMID |
| Employment status (ILRLEARNEREMPLOYMENT) | TRAINEEID and IERID |
| HE (ILELEARNERHE) | TRAINEEID, POT and SWSUPAIMID (via ILRLEARNERAIMS) |
| Learning delivery FAM for LSF, ALB and ACT (ILRLEARNDELFAM) * | TRAINEEID, POT, FAMTYPE and FAMTYPEID |
| Work placement (ILRLEARNERDELWORKPLACEMENT) | TRAINEEID, POT, WORKPLACENO and SWSUPAIMID (via ILRLEARNERAIMS) |
| App financial record (ILRTRAILBLAZERSFINANCIAL) | TRAINEEID, POT, FINTYPE, FINCODE, FINDATE and SWSUPAIMID (via ILRLEARNERAIMS) |
| Destinations (ILRLEARNERDESTINATIONS) | TRAINEEID, ULIN and DESTNO |
* Only LSF, ALB and ACT are included in this entity. Other FAM types are included in the learning delivery entity.
When rows for an entity match on ALL the fields specified above, the fields on those rows are compared for differences.
When a row cannot be matched on all specified fields, all the fields on that row are counted as a difference if they have a value entered.