Reviewing Results
Last updated
Was this helpful?
Last updated
Was this helpful?
After performing a file comparison, the resulting comparison table can be queried to view the results.
Most EDFacts files contain records with an “amount” column – a count of students or personnel. This is the column that is compared between the Legacy and the Generate files.
In all cases, you can view the results of a comparison by running the following query in SSMS:
Where [Comparison Table Name] is the fully qualified table name (database.schema.tablename) that contains the results. For example, the table might be named “Generate.NH.C029_LEA_2023_COMPARISON”.
You can then filter the results as needed to determine which records have mismatches to be addressed.
The C029 file contains many columns of data that must be compared. The file comparison table that is created by the utility includes a Legacy column and a Generate column for nearly every column in the C029 file.
The table below compares records from the Legacy and Generate files, joined by SubmissionYear, ReportCode, ReportLevel, and StateSchoolIDNumber. The count of Errors in the row and a description of the Mismatches are indicated in subsequent columns. Each remaining column pairs data from the Legacy and Generate files, allowing you to easily spot discrepancies by scrolling across the table.
If a row has an Errors value = -1, this indicates the record exists in one of the files but not the other. The Mismatch column will then indicate if the record is not in the Legacy file or not in the Generate file.
The C039 Comparison table is similar to the C029 Comparison table but with fewer columns. In most cases all results for the C039 that don’t match will show “-1” for the Errors because a Grade Level ID may not exist in one of the files.
You can alter the query to filter the results to only show records where the Errors <> 0 to simplify your review. You can also create summary queries to view the scope of the errors. For example:
Nearly all other EDFacts files only need to compare the Amount value between the Legacy and the Generate files. Running the query to return results from the Comparison table will show various columns corresponding to the Category Sets in the EDFacts report. The “LegacyAmount” and “GenerateAmount” columns show the counts of students (or personnel, depending on the report) for each combination of Category Set groups.
If the LegacyAmount and GenerateAmount do not match, then additional research can be done to determine the reason for the difference. If a LegacyAmount is NULL, then that record is missing from the Legacy file. If a GenerateAmount is NULL, then that record is missing from the Generate file.
To reduce the number of results to review, you can filter out records where the LegacyAmount and GenerateAmount match.
When no students (or personnel) exist for a combination of attributes, a zero count is included in the EDFacts submission file. Generate does not currently store zero counts in the Generate report tables. Instead, any zero counts are added to the actual submission file when it is created from Generate. The File Submission Comparison Utility accounts for this as follows:
If the Generate file table is missing a row (zero count row), but the corresponding row in the Legacy table contains a zero, then the utility treats this as a zero for the Generate value in the Comparison table. This helps reduce the number of mismatched records and simply the review of the comparison results.
Given this logic, if a GenerateAmount is NULL, it means there are no students identified in that Category Set, and would result in a zero value in the Generate-produced submission file.
In order to do a comparison that fully compares zero counts in the Generate submission file, load the Generate submission file into a table and run the comparison using the "" instructions.
