Populate linking pairs - Revision history

Ttenbergen: documentation cleanup after yesterday's update

2021-08-04T14:40:34Z

documentation cleanup after yesterday's update

@@ Line 4: / Line 4: @@
 * be sure you have completed [[Pre-linking checks]]
 * 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]]
 == 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
-The process considers the output of [[Query linking_pairs_preList]].
+| full=
-== 2021-07 review ==
+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 iterating is the slow part, so how can we reduce it; eg by shifting some of the work into queries
+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 ===
@@ Line 30: / Line 21: @@
 * 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}}

Ttenbergen: /* Algorithm */

2021-08-04T03:29:47Z

Algorithm

← Older revision		Revision as of 22:29, 3 August 2021
Line 35:		Line 35:
	* delete any non-consecutive records		* delete any non-consecutive records
	* add any new records		* add any new records

			{{Todo
			\| who = Tina
			\| todo_added = 2021-08-03
			\| todo_action = 2021-08-03
			\| question = I still need to document the new process properly, e.g. [[Query linking_pairs_preList]], [[Query linking_pairs_LatestLinkedPHINAdmit]]
			[[Query linking_pairs_multis]]
			[[Query linking_pairs_out_of_sequence/linking_pairs_out_of_sequence_deleter]]
			[[Query linking_pairs_unlinkeds]]
			}}


	{{Collapsable		{{Collapsable
Line 47:		Line 58:
	Run the populate linking code, but run it only for profiles that aren't in pairs		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		* run the linking pairs updater only for profiles that don't have

	Second draft.		Second draft.
	* Find every D_ID not in [[Linking pairs table]], and delete all other/existing records from [[Linking pairs table]] for that PHIN		* Find every D_ID not in [[Linking pairs table]], and delete all other/existing records from [[Linking pairs table]] for that PHIN

Ttenbergen: /* Algorithm */

2021-07-29T19:06:19Z

Algorithm

← Older revision		Revision as of 14:06, 29 July 2021
Line 32:		Line 32:

	=== Algorithm ===		=== Algorithm ===
	Clean our only those pairs that are questionable:		* add any new records as if all was good
			* delete any non-consecutive records
			* add any new records

			{{Collapsable
			\| always= previous thoughts
			\| full= Clean our only those pairs that are questionable:
	* find all records whose pairs have non-consecutive (admit dttms)		* 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		** need to admit this with the old style admit dttm included so the wholesale recreation will still work
Line 46:		Line 52:
	* Delete all [[Linking pairs table]] that have non-consecutive admit dttms		* 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]]		* re-populate [[Linking pairs table]] considering only D_IDs not already in [[Linking pairs table]]
			}}

	== Related articles ==		== Related articles ==

Ttenbergen: /* Algorithm */

2021-07-28T20:10:11Z

Algorithm

← Older revision		Revision as of 15:10, 28 July 2021
Line 41:		Line 41:
	Run the populate linking code, but run it only for profiles that aren't in pairs		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		* 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 ==

Ttenbergen: /* Implementation */

2021-07-28T15:24:52Z

Implementation

← Older revision		Revision as of 10:24, 28 July 2021
Line 10:		Line 10:
	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]].		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]].

	The process considers the output of		The process considers the output of [[Query linking_pairs_preList]].
	~~::SELECT L_PHI.PHIN, L_Log.Arrive_DtTm, L_Log.D_ID, IIf(Not IsNull(~~[~~accept_dttm]),~~[~~accept_dttm~~]~~,[arrive_dttm~~]~~) AS adm~~
	~~::FROM L_Log INNER JOIN L_PHI ON L_Log~~.~~D_ID = L_PHI.D_ID~~
	~~::ORDER BY L_PHI.PHIN, IIf(Not IsNull([accept_dttm]),[accept_dttm],[arrive_dttm]);~~

	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.		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.

Ttenbergen: /* Related articles */

2021-07-28T15:24:12Z

← Older revision		Revision as of 10:24, 28 July 2021
Line 49:		Line 49:

	[[Category:Data Processing]]		[[Category:Data Processing]]
	~~[[Category:Multiple encounter longitudinal consistency check\|*]]~~
	[[Category:Multiple Encounter linking]]		[[Category:Multiple Encounter linking]]
			[[Category:Centralized data front end.accdb]]

Ttenbergen: /* Algorithm */

2021-07-28T14:40:47Z

Algorithm

← Older revision		Revision as of 09:40, 28 July 2021
Line 37:		Line 37:
	Clean our only those pairs that are questionable:		Clean our only those pairs that are questionable:
	* find all records whose pairs have non-consecutive (admit dttms)		* find all records whose pairs have non-consecutive (admit dttms)
	** need to admit this with the old style admit dttm included so the ~~wholsale~~ recreation will still work		** 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 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		* 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 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		* run the linking pairs updater only for profiles that don't have

	== Related articles ==		== Related articles ==

Ttenbergen at 21:24, 27 July 2021

2021-07-27T21:24:51Z

← Older revision		Revision as of 16:24, 27 July 2021
Line 16:		Line 16:

	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.		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.

			== 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 ===
			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)

			=== 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 wholsale 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

			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

	== Related articles ==		== Related articles ==

Ttenbergen at 20:57, 27 July 2021

2021-07-27T20:57:49Z

← Older revision		Revision as of 15:57, 27 July 2021
Line 20:		Line 20:
	{{Related Articles}}		{{Related Articles}}

	[[Category: Data Processing]]		[[Category:Data Processing]]
	[[Category: Multiple encounter longitudinal consistency check\|*]]		[[Category:Multiple encounter longitudinal consistency check\|*]]
			[[Category:Multiple Encounter linking]]

Ttenbergen: /* Implementation */

2019-05-22T00:19:49Z

Implementation

← Older revision		Revision as of 19:19, 21 May 2019
Line 8:		Line 8:

	== 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]].		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]].

			The process considers the output of
			::SELECT L_PHI.PHIN, L_Log.Arrive_DtTm, L_Log.D_ID, IIf(Not IsNull([accept_dttm]),[accept_dttm],[arrive_dttm]) AS adm
			::FROM L_Log INNER JOIN L_PHI ON L_Log.D_ID = L_PHI.D_ID
			::ORDER BY L_PHI.PHIN, IIf(Not IsNull([accept_dttm]),[accept_dttm],[arrive_dttm]);

			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.

	== Related articles ==		== Related articles ==