Edit Data Integrity Check: Query NDC Bad Postal Code

Are you logged on?

You do not have permission to edit this page, for the following reason:

The action you have requested is limited to users in the group: CCMDB_legit.

Summary (what is the check, one-liner):
links to related pages:
Firmness (is this a warning only (soft check) or will it stop sending (hard check) - applicable only in CCMDB.accdb app):	None/unknown hard check soft check
Timing (does this query run for all records or only complete ones):	None/unknown complete always
Name of the function or query this is implemented as:	query ''NDC_Bad_Postal_Code''
Implementation date:
Implementation Status:
L_Problem (are false positive special cases addressed in table L_Problems - applicable only in CFE):	None/unknown Yes No
App:
Backlogged (is this query cleaned up to date or backlogged? Applicable only in CFE):

Free text:

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:Postal Codes]] [[Category:Registry checks]]

Explain your edit, if you think it needs explanation (e.g. "as discussed with ...", or "content moved to ...", or "duplicate content, see ..."):

This is a minor edit

Watch this page

Cancel