Diagnostics
Diagnostics are provided in the form of ICD-10 codes.
Objective of the code is to cleanse the data, assign serials together with regions, match the code with PE's code db and to process any duplicates. This is carried out in steps 1 to 6.
Prerequist MySQL tables are mandatory and are used to refer to:
1.Table hospital_serial contains PurpleXtra's serial number against the hospital name and where the serial number is a `x` indicates that the data is invalid i.e. the hospital is closed.
2.hospitals_norway
3.hospitals_from_pe_db
4.icd10_4_pe_db
5.icd10_4_unmatched_code_fix
6.icd10_4_unwanted_codes
Step 1:
Renames existing columns and adds a serial column.
Populates the serial column.
The following MySQL query updates table1 serial column from table2 on condition that the hospital names match. You will need to replace the table names with the corresponding ones - table2 will probably be called hospital_serial
UPDATE `table1`, `table2`
SET `table1`.`serial` = `table2`.`serial`
WHERE `table2`.`hospital` = `table1`.`hospital`;
Step 2:
Clean up the diagnostic data by:
- Checks the serial columns don't contain a zero value. If there are the script will abort and the hospital name will need to be checked against our (PE) database.
- Delete the rows where the serial = an x:
DELETE FROM `0diagnostics` WHERE `serial`='x';
- Removes rows where there are unwanted codes
Step 3:
Verify the Norwegian small cases policy and then convert the character to a valid number. The follow queries will populate the cases column by generating a random integer between 1 and 4:
UPDATE `0diagnostics` SET `cases` = FLOOR(1 + RAND() * (4 - 1 +1)) WHERE `cases` = '*';
Step 4:
1.Deal with any duplicates
2.Delete hospital column
3.Add region column
4.Populate the region column
5.Check for any blank cells
Step 5:
Ensure codes match PE database codes
Step 6:
Run Step 6 - Data Validation v0.1.php
1.Create table called step_6_diagnostics
2.Check / process any duplicates
3.Check for any blank cells
4.Count number of regions in final table
5.Display minimum and maximum code length
6.Change cases column from VARCHAR to INT
7.Check the total cases match in original and final tables
Topic last reviewed: 12/07/25