CCMDB Wiki

Query NDC Bad Postal Code: Difference between revisions

← Older edit
→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.
 
(38 intermediate revisions by 3 users not shown)
Line 1: Line 1:
We cross-check the postal codes we collect as part of [[Postal Code field]] 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 ==
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:  
* if the patient has '''[[Province]] = Manitoba''':  
* if the patient has '''[[Province]] = Manitoba or CF''' :  
*# check the postal code in EPR, correct if different than ours
*# 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];  
*# 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'''
*#* update patient's postal code if it was wrong '''or'''
*#* make [[#update to Table Postal_Code_Master]] if postal code was correct and just not in our list
*#* 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 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 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 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) the query should not trigger at all
* if the patient has '''Province = (US or NK or OS)''' there should be no postal code so query should not trigger at all


=== province = CF ===
=== update to [[Postal_Code_Master table]] ===
{{discussion}}
''see [[Postal_Code_Master table#Maintenance and Validation of Master table]] for other update processes we have for [[Postal_Code_Master table]]''
* What if pt has Province=CF? ones should be tested as they would have postal codes, quite possibly MB ones, need to discuss
# Open [[Postal_Code_Master table]]  
 
=== update to [[Table Postal_Code_Master]] ===
# Open [[Table Postal_Code_Master]]  
# update/add code
# update/add code
#* you only need to fill in the fields '''PCode, rhaname, RHA_Acronym and town''', we will eliminate the other fields.
#* 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==
* From Randy Martens to [[Julie Mojica]]
integrated with
* http://geocoder.ca/?freedata=1
 
==Wikipedia Postal codes listings across Canada==
[http://en.wikipedia.org/wiki/List_of_postal_codes_in_Canada Wikipedia, Postal codes in Canada]
*at the top of the article shows the the first letter that a postal code starts with for a province.
 
[http://www.canadapost.ca/cpc2/addrm/hh/maps/FSA/CA01.pdf Map showing first letter used in postal codes across Canada]


==RHA Listings==
== Log ==
* see [[Manitoba RHAs]]
* 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


==Start Date==
== Related articles ==
2014-09-29
{{Related Articles}}
Cleaning to start end of August 2016




[[Category: Data Processing]]
[[Category:Data Processing]]
[[Category: Postal Codes]]
[[Category:Postal Codes]]
[[Category:Registry checks]]
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Query_NDC_Bad_Postal_Code"