Query NDC Bad Postal Code: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
← Older edit
m sp
→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.
 
(52 intermediate revisions by 3 users not shown)
Line 1: Line 1:
We cross-check the postal codes we collect as part of [[Postal code collection]] against a list of known-good postal codes.  
{{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 ==
== CFE Data Integrity Checks Steps ==
=== Comparison ===
Comparisons are done as part of [[vetting]] via NDC query ''NDC_Bad_Postal_Code'' in [[CFE]].
Comparisons are done as part of [[vetting]] via NDC query ''NDC_Bad_Postal_Code'' in [[CFE]].
Steps will be:  
Steps will be:  
<we need to fill these in when we have completed the first-time updates, we deferred decision on the steps today to when we have done partial cleaning and know how much is left. booked to do with Pagasa on Monday. Ttenbergen 16:50, 2014 October 2 (CDT)> {{discussion}}
* 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


=== updating the codes ===
=== update to [[Postal_Code_Master table]] ===
Codes are stored in table ''Postal_Code_Master''. Codes can be manually added to or imported into this table when a new master set becomes available.  
''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.


== File Locations ==
== Maintenance and Validation of Master table ==
old master imports can be found at
see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]''
X:\CCMDB\z_archive\PostalCode


==Where was it obtained==
== Log ==
* From Randy Martens to [[Julie Mojica]]
* 2021-01-27 - changed query to only look at the last year of data to make it faster; any older records were already fixed
integrated with
* 2016-08-end - cleaning started
* http://geocoder.ca/?freedata=1
* 2014-09-24 - implemented


==Start Date==
== Related articles ==
2014-09-29
{{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
  • Cargo


  • SMW


  • Categories:  
  • form:

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 :
    1. check the postal code in EPR, correct if different than ours
    2. if same as ours, check the address in EPR against Canada Post Postal Codes;
  • 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

  1. Open Postal_Code_Master table
  2. 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:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Query_NDC_Bad_Postal_Code&oldid=167261"