Manitoba Health "Conditioner"

From CCMDB Wiki
Jump to navigation Jump to search

Legacy Content

This page contains Legacy Content.

Click Expand to show legacy content.

The Manitoba Health (MB Health) "Conditioner" is an MS Access Program that facilitates Crosschecking data with Manitoba Health using the Manitoba Health Match Indicator Guideline.

What does it do?

  • automates the process of identifying corrections to key patient identifiers (PHIN, LN, FN, DOB & Gender) from an EXCEL file that is sent every few months to MB Health for crosschecking purposes and is returned with matches that require corrections in our Master ICU/Medicine Master Database.
    • items highlighted in RED = errors
    • items highlighted in GREEN = no errors

Where do you find it?

  • M:\​MB_Health_Records_Cleaning (full address: \\ad.wrha.mb.ca\wrha\HSC\shared\WRHA\WRHA_CCMED\MB_Health_Records_Cleaning)
  • a backup copy of conditioner.mdb is also in this directory - PTorres 15:20, 2014 May 29 (CDT)

Pagasa also keeps a backup copy of the above folder and all its contents her C drive

What files does it read?

  • Excel data files that have been sent to and returned from MB Health by Courier which is on a (encrypted/password protected) CD.

What is in the Excel files that is sent and then returned?

  • Study, Chart, PHIN, LN, FN, DOB & Sex - MH_PHIN2, MH_LName, MH_FName, MH_DOB, MH_Sex, matched kind (0 to 2)

What do the "matchkind" indicators mean when data is returned from MB Health?

Matched Indicators:

  • 0 = no match made with MHSC data
  • 1 = matched with registry using PHIN
  • 2 = matched with registry not using the PHIN (either LN, DOB.)

See Manitoba_Health_Match_Indicator_Guideline for decision making Guidelines for match and unmatched data recieved from Manitoba Health.

Where are the Excel Data Files located

  • S:\MED\CCMED\​S:\MED\CCMED\CCMDB_ADMIN_DATABASE\MB_Health_Records_Cleaning\MBHealth

How are EXCEL files labeled?

  • sent files are labeled as follows:
    • ICU EXAMPLE: AU.xls (AU.zip)
    • MED EXAMPLE: MedX.xls
  • returned files are labeled as follows:
    • ICU EXAMPLE: AUout.xls
    • MED EXAMPLE: Med_Xout.xls
  • paper log file is kept of sent/recieved batch numbers for ICU and Medicine


Procedure when DATA CD is returned from MHSC

  • copy password protected zip file into file folder on X that contains original sent file
  • unzip MHSC out.xls file into same folder
  • Open out.xls file
  • NOTE: AS of OCT 10.08 batch AVout.xls ICU and med_Xout.xls: changed of format of "our" DOB column of data from YYYY-M-D to M/D/YYYY format. (see instruction how to do this below). This is a work around for now until Julie sorts out with MHSC if they can revert to previous output format for our DOB column.
  • also you must change the header row label MH_PHIN2. Remove the 2, change to MH_PHIN. If this change is not made the "Conditioner" Program will not read file correctly.
  • Save change
  • Exit
  • now you can use MHSC CONDITIONER Program which will import and read the output MHSC file correctly.
  • file DISK in MHSC crosschecking binder

November,19.08 "Conditioner" ACCESS Program Problems

PROBLEM:

  • 1. No one working on it to further improve it.
  • 2. No designated person is currently responsible for this program, nor has shown anyone else how to maintain it.
  • 3.Conditioner Program is unable to read the MHSC output EXCEL file correctly, specifically "our" DOB data column & PHIN column. This is because when MHSC sends the data back to use, "our" DOB and PHIN columns are fomatted to text instead of numeric. Not sure why they do that.
  • October 10.08 - The EXCEL Batch sent to MHSC for crosschecking: included a few more columns of data: admit and discharge. Purpose was to hopefully allow us to obtain Hosp mortality data if available.
  • The EXCEL files returned from MHSC (MedXout.xls and ICU, AVout.xls) we noted that in our original DOB column, the format was changed from M/D/YYYY to YYYY-M-D. Because of this, the "Conditioner Program" could not read the file properly and was reporting error every DOB data.

REPAIR SOLUTION:

  • Julie will advise the contact person at MHSC about format change on the output file. We want to retain same DOB format for our "original" DOB column of data which is M/D/YYYY. (MH_DOB column is fine as is).
  • In the mean time we reformatted our DOB data column back to the M/D/YYYY format and Conditioner Program worked.
  • hopefully the next batch we send to MHSC will be returned without this problem.
  • January 22.09 - MHSC still sending data with these to columns altered.
  • Pagasa needs clearly documented instruction to follow on how to reformat columns from TXT to numeric.


INSTRUCTION for changing the format of a COLUMN of data in EXCEL from NUMERIC to TEXT

  • MHSC- "OUT" -is the name of the file which is returned from MB Health.
    • when we send data to MB Health, the HSC columns of data for PHIN and DOB are NUMERIC (justified to the RIGHT).
    • When data file is returned from MB Health, the HSC column of data for PHIN is retained as NUMERIC and the DOB is changed to TEXT (justified to LEFT).
    • MB Health columns of data for MB_PHIN and MB_DOB columns of data are both TEXT (justified to LEFT)
  • For MB Conditioner Program to work the following columns of data must be checked:
  1. the HSC column for PHIN must be changed to TEXT (justified to left).TOstryzniuk 14:22, 23 June 2009 (CDT)

If conditioner doesn't run with the above fix then check the following:

  1. the "HSC" column for PHIN & DOB should both be TEXT (justified to the LEFT).
  2. "MHSC" column for MB_PHIN & MB_DOB should both be TEXT (justified LEFT).
  3. Header Row must be the same. (Pagasa copies header row from previous MBH OUT to current MBH OUT. Save. Exit. Now run MB Conditioner).
  • NOTE: Julie's comment:
  • if "numbers" in a column are justified to the LEFT, the data in that column is stored as TEXT.
  • If numbers in a "column" are justified to the RIGHT, the data in that column is stored as NUMERIC.
  • NOTE: that if number field has a small green tag in it, click on that. It is a comment for that field. It will tell you that the number is stored as a TEXT. TOstryzniuk 18:41, 23 January 2009 (CST)


  • for MHSC output.xls
  • This instruction starts for batch MHSC output data (ICU_AVout.xls & MedXout.xls)& onward.
  • Problem: When MHSC returns crosschecked data back to us, "our" DOB and PHIN column has been altered.
  • 2 problems:
  1. DOB is changed to YYYY-M-D back from the original M/D/YYYY that we sent them.
  2. DOB and PHIN columns of data are not sent back in consistent format. e.g. HSC PHIN is numeric and MB_PHIN is text format.
  • the ACCESS "Conditioner Program" will not run if we don't restore the correct format of these columns.

A. how to change "our" sent DOB column format from YYYY-M-D back to M/D/YYYY B. how to change "our" PHIN column format from text to numeric.

  1. Open MHSC_out.xls file
  2. Select the whole DOB column (this is our DOB column, not the MH_DOB column)
  3. Go to DATA menu tab at top of EXCEL screen
  4. Select "text to column" from drop down menu
  5. Press NEXT at bottom of this window
  6. Press NEXT again
  7. Top Right of this window you will see COLUMN DATE FORMAT
      • select DATE item on menu
      • from the drop down list beside DATE, select: M/D/Y format
  8. Press FINISH

B. how to change "our" PHIN column format from numeric to text:

  1. Open MHSC_out.xls file
  2. Select the whole PHIN column (this is "our" PHIN column, not the MH_PHIN column)
  3. Go to DATA menu tab at top of EXCEL screen
  4. Select "text to column" from drop down menu
  5. Press NEXT at bottom of this window
  6. Press NEXT again
  7. Top Right of this window you will see General format
  8. Press FINISHED
  9. Don't forget to save changes.
  10. Exit Excel
  11. MHSC Conditioner Program will now be able to read this Excel file.

TOstryzniuk 20:06, 19 November 2008 (CST) TOstryzniuk 20:49, 22 January 2009 (CST)UPDATED.


Discussion

      • I see that there is a another page started with Crosschecking data with Manitoba Health. I didn't realize it was there. Some of this information list here under MB Health CONDITIONER program will need to be moved over there. TOstryzniuk 17:56, 20 November 2008 (CST)
  • tina.....feel free to move things. 
Legacy info only below here.

iFolder

ifolder was set up for the purpose of exchanging information, however MB Health would not exchange files in this manner.

Who Developed it?

  • ACCESS Conditioner Program developed by Tina as requested by Julie.
  • DATE: January, 2005

Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Manitoba_Health_%22Conditioner%22&oldid=163333"