Query NDC Bad Postal Code: Difference between revisions
Jump to navigation
Jump to search
TOstryzniuk (talk | contribs) m Main office - data vetting process |
Ttenbergen (talk | contribs) →update to Postal_Code_Master table: removed old question and the incomplete steps that it was about, the instructions here look like they would be sufficient. |
||
| (61 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
== | {{Data Integrity Check | ||
|DIC_summary=Cross check of [[Postal Code field|postal codes]] against known postal codes | |||
|DIC_related_concepts=Postal Code field | |||
|DIC_firmness=hard check | |||
|DIC_coding=query ''NDC_Bad_Postal_Code'' | |||
|DIC_implementation_date=2014-09-24 | |||
|DIC_status=implemented | |||
|DIC_LProblem=No | |||
|DIC_app=Centralized data front end.accdb | |||
|DIC_backlogged=No | |||
}} | |||
We cross-check the postal codes we collect as part of [[Postal Code field]] collection against a list of known-good postal codes. Also see [[query NDC_Postal_Code_over_time]]. | |||
== | == CFE Data Integrity Checks Steps == | ||
Comparisons are done as part of [[vetting]] via NDC query ''NDC_Bad_Postal_Code'' in [[CFE]]. | |||
Steps will be: | |||
* if the patient has '''[[Province]] = Manitoba or CF''' : | |||
*# check the postal code in EPR, correct if different than ours | |||
*# if same as ours, check the address in EPR against [https://www.canadapost.ca/cpo/mc/personal/postalcode/fpc.jsf Canada Post Postal Codes]; | |||
*#* update patient's postal code if it was wrong '''or''' | |||
*#* make [[#update to Postal_Code_Master table]] if postal code was correct and just not in our list | |||
* if the patient is from '''any other province''': | |||
** if the Postal Code's '''first letter''' is not for the province where the patient lives you need to check EPR or internet to put the correct Postal code | |||
** If the first letter is appropriate for province but triggers because it's not in the master list, then register the D_ID with [[Known data errors]] with query name "NDC_Bad_Postal_Code" | |||
* if the patient has '''Province = (US or NK or OS)''' there should be no postal code so query should not trigger at all | |||
== | === update to [[Postal_Code_Master table]] === | ||
''see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]'' | |||
# Open [[Postal_Code_Master table]] | |||
# update/add code | |||
#* you only need to fill in the fields '''PCode, rhaname, RHA_Acronym and town''', we will eliminate the other fields. | |||
== | == Maintenance and Validation of Master table == | ||
see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]'' | |||
* | == Log == | ||
* 2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed | |||
* 2016-08-end - cleaning started | |||
* 2014-09-24 - implemented | |||
== | == Related articles == | ||
{{Related Articles}} | |||
[[Category: Data Processing]] | |||
[[Category:Data Processing]] | |||
[[Category:Postal Codes]] | |||
[[Category:Registry checks]] | |||
Latest revision as of 10:44, 4 August 2025
| Data Integrity Checks | |
| Summary: | Cross check of postal codes against known postal codes |
| Related: | Postal Code field |
| Firmness: | hard check |
| Timing: | always |
| App: | Centralized data front end.accdb |
| Coding: | query NDC_Bad_Postal_Code |
| Uses L Problem table: | No |
| Status: | implemented |
| Implementation Date: | 2014-09-24 |
| Backlogged: | No |
We cross-check the postal codes we collect as part of Postal Code field collection against a list of known-good postal codes. Also see query NDC_Postal_Code_over_time.
CFE Data Integrity Checks Steps
Comparisons are done as part of vetting via NDC query NDC_Bad_Postal_Code in CFE. Steps will be:
- if the patient has Province = Manitoba or CF :
- check the postal code in EPR, correct if different than ours
- if same as ours, check the address in EPR against Canada Post Postal Codes;
- update patient's postal code if it was wrong or
- make #update to Postal_Code_Master table if postal code was correct and just not in our list
- if the patient is from any other province:
- if the Postal Code's first letter is not for the province where the patient lives you need to check EPR or internet to put the correct Postal code
- If the first letter is appropriate for province but triggers because it's not in the master list, then register the D_ID with Known data errors with query name "NDC_Bad_Postal_Code"
- if the patient has Province = (US or NK or OS) there should be no postal code so query should not trigger at all
update to Postal_Code_Master table
see Postal_Code_Master table#Maintenance and Validation of Master table for other update processes we have for Postal_Code_Master table
- Open Postal_Code_Master table
- update/add code
- you only need to fill in the fields PCode, rhaname, RHA_Acronym and town, we will eliminate the other fields.
Maintenance and Validation of Master table
see Postal_Code_Master table#Maintenance and Validation of Master table for other update processes we have for Postal_Code_Master table
Log
- 2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed
- 2016-08-end - cleaning started
- 2014-09-24 - implemented
Related articles
| Related articles: |