Populate linking pairs: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
m (documentation cleanup after yesterday's update)
 
(2 intermediate revisions by the same user not shown)
Line 4: Line 4:
* be sure you have completed [[Pre-linking checks]]
* be sure you have completed [[Pre-linking checks]]
* click the "populate linking_pairs" button on the main screen
* click the "populate linking_pairs" button on the main screen
**2019-Feb-07:  NOTE:  takes ~08 minutes to run
** this takes ~3 minutes to run (as of 2021-08-04)
* After this is done, move on to [[Correcting suspect links]]
* After this is done, move on to [[Correcting suspect links]]


== Implementation ==
== Implementation ==
Because MS Access doesn't have functionality to compare data in a "current" to a "next" record this is implemented as an iterative process in Module ''linking'' Sub ''populate_linking_pairs'' which populates the [[linking_pairs table]].
{{Collapsable
| always= click expand to see implementation details
| full= 


The process considers the output of [[Query linking_pairs_preList]].
Because MS Access SQL doesn't have functionality to compare data in a "current" to a "next" record this is implemented as an iterative process in VBA in Module ''linking'' Sub ''populate_linking_pairs'' which populates the [[linking_pairs table]].


For any line that has the same PHIN as the previous line it generates a record in [[linking_pairs table]] of the two D_IDs.
The process considers the output of [[Query linking_pairs_preList]], which contains records that are not in [[linking_pairs table]], and that have more than one instance of a [[PHIN]]. After these are linked, if there are any non-sequential entries in linking pairs, these are deleted by [[Query linking_pairs_out_of_sequence/linking_pairs_out_of_sequence_deleter]], and then their entries are recreated. That happens until all are sequential.
 
== 2021-07 review ==
{{Todo
| who = Tina
| todo_added = 2021-07-27
| todo_action = 2021-07-27
| question =
* not implemented yet, the following is just the plan to go with
}}
 
The iterating is the slow part, so how can we reduce it; eg by shifting some of the work into queries


=== Scenarios ===
=== Scenarios ===
Line 30: Line 21:
* an older record(s) is changed, requiring a re-alignment (the <2% case)
* an older record(s) is changed, requiring a re-alignment (the <2% case)
* a collector catches up with backlog, and 2 records that had already been linked 1-3 need to be re-linked to 1-2-3 (the 3% case)
* a collector catches up with backlog, and 2 records that had already been linked 1-3 need to be re-linked to 1-2-3 (the 3% case)
 
}}
=== Algorithm ===
Clean our only those pairs that are questionable:
* find all records whose pairs have non-consecutive (admit dttms)
** need to admit this with the old style admit dttm included so the wholesale recreation will still work
* remove all pairs related to those patients (ie have same PHIN)(easier than figuring out which to remove)
* remove all pairs where a pair-less profile is present for the same phin
** this would include old single encounters (57% of our records are single encounters)
 
Run the populate linking code, but run it only for profiles that aren't in pairs
* run the linking pairs updater only for profiles that don't have
 
Second draft.
* Find every D_ID not in [[Linking pairs table]], and delete all other/existing records from [[Linking pairs table]] for that PHIN
* Delete all [[Linking pairs table]] that have non-consecutive admit dttms
* re-populate [[Linking pairs table]] considering only D_IDs not already in [[Linking pairs table]]
 
== Related articles ==
== Related articles ==
{{Related Articles}}
{{Related Articles}}

Latest revision as of 09:40, 2021 August 4

As part of vetting CFE needs to pair up any records for the consecutive/continuous admission of a patient.

Instructions

  • be sure you have completed Pre-linking checks
  • click the "populate linking_pairs" button on the main screen
    • this takes ~3 minutes to run (as of 2021-08-04)
  • After this is done, move on to Correcting suspect links

Implementation

click expand to see implementation details   

Because MS Access SQL doesn't have functionality to compare data in a "current" to a "next" record this is implemented as an iterative process in VBA in Module linking Sub populate_linking_pairs which populates the linking_pairs table.

The process considers the output of Query linking_pairs_preList, which contains records that are not in linking_pairs table, and that have more than one instance of a PHIN. After these are linked, if there are any non-sequential entries in linking pairs, these are deleted by Query linking_pairs_out_of_sequence/linking_pairs_out_of_sequence_deleter, and then their entries are recreated. That happens until all are sequential.

Scenarios

Two things will make a record require a pairs update:

  • a new record is added (the >95% case)
  • an older record(s) is changed, requiring a re-alignment (the <2% case)
  • a collector catches up with backlog, and 2 records that had already been linked 1-3 need to be re-linked to 1-2-3 (the 3% case)

Related articles

Related articles: