Instructions for importing a batch of DSM Data: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
Line 61: Line 61:


=== queries for which we have no page yet ===
=== queries for which we have no page yet ===
=== D_ID Related Issues ===
 
==== Duplicate RequestNo For Incomplete Profiles sent more than once ====
=== D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once ===
This is caused by patients having more than one D_ID because  
This is caused by patients having more than one D_ID because  
# The location portion of the D_ID has been changed after the requestor list has been sent to DSM.   
# The location portion of the D_ID has been changed after the requestor list has been sent to DSM.   
# The D_ID format has been changed to include Laptop Identifier.  
# The D_ID format has been changed to include Laptop Identifier.  
Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.  
Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.  
==== Missing Lab data for Completes ====
=== D_ID Related Issues - Missing Lab data for Completes ===
This is also caused by the change in the D_ID after the requestor list has been sent to DSM.
This is also caused by the change in the D_ID after the requestor list has been sent to DSM.
# The location portion of the D_ID has been changed.  
# The location portion of the D_ID has been changed.  
Line 75: Line 75:
Found cases where Lab dates between Accept and Arrived are missing.
Found cases where Lab dates between Accept and Arrived are missing.
# This may be due to the changes done in the dates.
# This may be due to the changes done in the dates.
=== Duplicates due to Inconsistent Lab labels ===
Found three labs that are not labeled the same
# 'Arterial Lactat ' vs 'Arterial Lactate'
# 'Blood Gas Gluco ' vs 'Blood Gas Glucos'
# 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'


== Known issues ==
== Known issues ==

Revision as of 15:56, 2020 November 5

see Instructions for requesting a batch of data from DSM for counterpart

We receive data from DSM that needs to be imported into our main repository.

Instructions

Prep

  1. copy files to local, they are large and won't work well on a share.
  2. copy the newly received batch of data from DSM_Lab_Extract#File_Share to a different local directory that contains only those filesnot the requestor file.
  3. open DSM_Labs_Consistency_check.accdb

Check for new labs

  1. Open DSM_Labs_Consistency_check.accdb
  2. Click the "Check DSM .csv for new labs " button
  3. pick the directory with the new data files
  4. wait for the check to complete, about half an hour
  5. if the program tells you there are new labs, review them and ask Trish or Allan if any of them should be considered as part of our counts; click "No" for this
    • open the following queries (they are based on labs imported within the last hour)
      • s_group, sort by "added_dt" and review today's only
      • s_mapping_lab, sort by "date_listed" and review today's only
    • If any of the new ones are identified by Trish or Allan as needing to be counted then you have to ask Tina or Julie to add them to the list;
    • If NO additional lab, just close it.

actual data import

If the above listed no new labs, or the new labs were resolved, click the "Import DSM .csv" button and follow essentially the same steps as above to import the actual data. Close the file

Run consistency checks

  • Open DSM_Labs_Consistency_check.accdb
  • Open query Import_request_matcher
  • Get the Number of D_ID with no match lab DSM by following the steps below.
    • Go to design view and add the Import DtTm
    • SentDtTm column on criteria put the date you want to ran and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)
    • Go to pivot table
    • Go to Datasheet view and sort lowest to highest then filter to blanks only
    • Go to External Data then click excel
    • Go to X CCMDB DSM Lab imports Archive_raw then to the folder you want to save
    • Click Export data with formatting and layout then rename Ex. (No_Match_DSM_450.xlsx) then save.

Cleanup

Move files to their permanent homes. Make sure you do all of these!

  1. Make a new directory in \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\archive_processed and call it e.g. DSM_2019-03-27
    1. copy (not move) DSM_Labs_Consistency_check.accdb to that new directory
    2. copy (not move) DSM Labs data.accdb to that new directory
  2. move (not copy) DSM_Labs_Consistency_check.accdb and DSM Labs data.accdb from your computer to \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\ (not the archive) - this is the master version.
  3. move (not copy) the folder of data you processed from the directory it arrived where we share with DSM to the archive_raw directory in the same folder (these are big so they really should be moved, not copied and left in two places)

Log import

Update the log at Instructions for requesting a batch of data from DSM#Log.

Integrity checks after import

Data Integrity Checks (automatic list)

 AppStatus
Query Import request matcherDSM Labs Consistency check.accdbneeds review

queries for which we have no page yet

D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once

This is caused by patients having more than one D_ID because

  1. The location portion of the D_ID has been changed after the requestor list has been sent to DSM.
  2. The D_ID format has been changed to include Laptop Identifier.

Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.

D_ID Related Issues - Missing Lab data for Completes

This is also caused by the change in the D_ID after the requestor list has been sent to DSM.

  1. The location portion of the D_ID has been changed.
  2. The D_ID format has been changed to include Laptop Identifier.

When uploading the DSM data, the new D_ID is being used for finding matches while the dumped Lab data is identified by the old D_ID. While present in DSM dumped file, no match was found resulting to no lab data for that profile.

Missing some Lab Dates

Found cases where Lab dates between Accept and Arrived are missing.

  1. This may be due to the changes done in the dates.

Duplicates due to Inconsistent Lab labels

Found three labs that are not labeled the same

  1. 'Arterial Lactat ' vs 'Arterial Lactate'
  2. 'Blood Gas Gluco ' vs 'Blood Gas Glucos'
  3. 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'

Known issues

None at this time

How does it work

Log

Update the log at Instructions for requesting a batch of data from DSM#Log.

Related articles

Related articles: