VBA routine to match legacy source values with target values (challenging!!!)

[orange]

I did not look further at the autonumber in the LK* tables. I could if there is a need, but I don't see it yet.
Since there is no autonumber field in those tables, it could be inserted when you are processing. However, if you are constantly refreshing things then better to automate up front.

For the preliminary mapping, I chose some key strings to select "candidate records/injury types" into groups.
I ended up with 16 groups and mapped all 160 records.


I'm attaching the LK_INJURY_TYPE with the mapping identified in the comment field. The Mapping show which of my queries matched. (But the value may depend on the order the queries are run where keyword from >1 query was found).
So, I suggest you look at the groupings and check for some consistency. Then, see how you would map the queries/groups to the new target injuryType. As I said the groupings were meant to reduce the 160 to a smaller set for mapping to new target.

Once changes or approach is identified, I'll adjust the database and forward it to you.

I also tried to break each old injury type into fragments and same for the new ones, then tried to match fragment to fragment.
Quite frustrating --really only got a handful of hits/matches and most of those were on things like "and", "the". So I restricted possible mappings to at least 4 characters and got this list (not very helpful in my view). This didn't seem helpful to me without
a lot more work.

aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System
aold(i) contusion ~map ~ anew(j) Contusion
aold(i) Dislocation ~map ~ anew(j) Dislocation
aold(i) foreign ~map ~ anew(j) Foreign
aold(i) Foreign ~map ~ anew(j) Foreign
aold(i) Fracture ~map ~ anew(j) Fracture
aold(i) contusion ~map ~ anew(j) Contusion
aold(i) wound ~map ~ anew(j) Wound
aold(i) Hearing ~map ~ anew(j) Hearing
aold(i) nerves ~map ~ anew(j) Nerves
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) multiple ~map ~ anew(j) Multiple
aold(i) superficial ~map ~ anew(j) Superficial
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) injury ~map ~ anew(j) Injury
aold(i) fracture ~map ~ anew(j) Fracture
aold(i) wound ~map ~ anew(j) Wound
aold(i) Respiratory ~map ~ anew(j) Respiratory
aold(i) Respiratory ~map ~ anew(j) Respiratory
aold(i) respiratory ~map ~ anew(j) Respiratory
aold(i) respiratory ~map ~ anew(j) Respiratory
aold(i) Respiratory ~map ~ anew(j) Respiratory
aold(i) Respiratory ~map ~ anew(j) Respiratory
aold(i) FOREIGN ~map ~ anew(j) Foreign
aold(i) dislocation ~map ~ anew(j) Dislocation

[skydivetom]

Jack:

You've put a great amount of thinking/work into this... it goes w/o saying, I am very grateful for your assistance again.

I downloaded/reviewed the attached ASCII file. It appears it include the "data value" to query name mapping. I'm not sure what the following listed conditions/references mean at the moment...
aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System

Did you say you'll be posting a DB in this forum which would include the functionality of these queries? Also, did you you manually generate these 16 query groupings?

Standing by...

[orange]

I downloaded/reviewed the attached ASCII file. It appears it include the "data value" to query name mapping.

I reviewed the old data and created some queries to select records based on some keywords/fragments that seem (to me) to represent some common categorization. I named the query to be somewhat self identifying of the "grouping". And then updated the comments field with the query name. The groupings could be refined as needed. The next step in my view was to see if there was a mapping between these groupings and the values in the Target table.


I'm not sure what the following listed conditions/references mean at the moment...
aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) CONDITIONS ~map ~ anew(j) Conditions
aold(i) system ~map ~ anew(j) System
aold(i) system ~map ~ anew(j) System

These were the results of trying to map words in the old (LK_Injury_type) to individual words in the new proposed Injury_Types.

The matching was not very productive. Only a few matches, so I left that approach until the mappings above were reviewed. If the mappings above are sufficient, I'll post the database. But I don't want to get deeper into anything if it isn't productive.

[skydivetom]

I reviewed the 160 injuries... based on the current injury to "....Q" (query) mapping, all are mapped correctly (based on available keywords).

6 TwoCharAndNoneInjuryTypeQ
7 TwoCharAndNoneInjuryTypeQ
Abrasion on right knee, soreness right hip SoreQ
abrasion on the corneal to to the right eye EyeRelatedQ
abrasion to right index finger CutAbrasionNotEyeQ

Visualization (e.g., output of the routine in DB) would help me make a better judgement call though.

[orange]

I'm out for most of the day.
Here is database to do the mappings. If a record fits into more than 1 group, the group names are identified in the Comments field separated with "| ".
You can review the queries that do the mapping.
Comments welcome.

[skydivetom]

Jack -- first of all, thank you for sending the DB. I had a quick glance at it... I'll have to spend more time working w/ it to see how it's running/processing the info.

In the meanwhile, I had started the **MANUAL** data value mapping. Based on that tedious effort, I have populated both the TARGET_VALUE as well as TARGET_PK_FK fields. Ultimately, given the target values are distinct, I later will be will able to update the table with "TABLE_NAME" and "TABLE_FIELD"... which in this case will always be the same across the 160 records. Also, I've put the keyword (for validation purposes only) into the Comments field.

Point is, I now have the "old" and "new" values in my LK table. I am not sure (yet) how I can accomplish the same/similar output with the automated DB.

Am I overlooking something when running the DB?

[orange]

Back now. What is the status?

[skydivetom]

Jack:

Thank you for getting back with me. I have attached a screenshot which illustrates the *before* and *after*. Above each table I included 3 color-coded banners (yellow, green, blue).

Below summarizes the table and their current status:

1. Yellow:
a. This is the original LK (source table).
b. Please note that I added the new field [TARGET_VALUE].
c. Ultimately, though it only shows the field [INJURY_TYPE] being populated.

2. Green:
a. As indicated previously, I took a stab at manually matching source-to-target values.
b. Even though it was tedious (i.e., using a bunch of wild cards) in both source and target tables, I ultimately matched (well, I decided in the end) which target values best fit the source data.
c. Specifically, in the source table, field [TARGET_VALUE] now shows the corresponding value.
d. So, for example, I matched up, e.g., source record #15 "abrasive" with target value "Other Traumatic Injuries and Disorders" (in my comments, I added the keyword "trauma" which was used to make that decision).
e. In the end, all target value have been entered in the [TARGET_VALUE] field for all 160 records.

3. Blue:
a. Although I had already finished the manual mapping for those 160 records, I ran the automated procedure ("Clear Comments", "Run", "Show Latest", "List Query Name...")
b. While I was excited that the automated routine completed, I was a little surprised about the outcome.
c. That is, based on my understanding, the procedure added the query's name into the field [COMMENT]. However, no actual data mapping took place.
d. In other words, I had hoped that, e.g., "Other Traumatic Injuries and Disorders" (or any other possible picked value by the routine) would have been added to record #15 (in [TARGET_VALUE].

So, based on 3d., that's what I meant by my last post's comment "Am I overlooking something when running the DB?".

In summary, although I have finished the manual mapping of the 160 values for "injury types", I have still 70+ LK tables left that require similar mapping. Some of these tables will not be as straight-forward where I need to map
"abrasive" to "Trauma...". For example, there will be cases where I need to map ID numbers (legacy system) to ID number (target system). Naturally, for those, other principles apply.

However, there will be LK tables left (out of the remaining 70) which will fall into a similar procedure as the injury table. So, if the automation of the injury table would work, I probably could apply the *keyword match logic" to those tables.

That said, if there's a way to automate the injury table procedure where [TARGET_VALUE] is populated with actual target values, it'll be a real *game changer* for me.

I hope this makes sense... thoughts/recommendations on how to tweak the VBA routine (so that I end up with the **GREEN** table but in an automated way vs. the manual process)?

Thank you,
Tom

[orange]

?? Where are the Target_PK_FK values 125, 175 coming from??
You have mapped all 160 Old to new. How many of the new targets now have values?

I was trying to group by some keywords --you can see what was included in the Query SQL. I thought it might make things a little easier, but you have manually done this table. My approach would have used the groupings to help match the target terms. But I can see that direct mapping could be more efficient- especially if we had some description of what's and what's not in the new injury type.

Since there are a number of other files to be mapped, I'm sure it can be done. The key is to get the requirements in order to build a tool or 2 to assist the processes and is repeatable. Tool should handle several, if not all of the mappings.

Having done the manual mapping, do you have a better idea/template for a "tool" that could be used with any/all of the mapping to be done?

[skydivetom]

Target_PK_FK values (125, 175) were pulled from the target table. Those are actually the primary keys for each of the target system's injury types.

However, given the injury types (text) are DISTINCT, I can later link data either via text or PK #. I added those PK # after I posted the thread; however, given that the idea to match records based on text (not IDs), including/excluding them should not make a difference.

WRT to the mapping of the 160 records, I followed the concept of a left inner join. That is, I only need to make sure that I get the new values for the legacy values. Alternatively, not all target system values have to be mapped to the source system (right inner join).

As you know, I only have to find the corresponding placeholder in the new system in order to migrate old data.

And finally, WRT to the question "do you have a better idea/template for a 'tool' that could be used...?" Allow me to use an analogy:

- As part of the original post I mentioned the "green apple (Granny Smith)" to "Apples" comparison. So, now that I've completed the injury types, I've a better understanding about "fruits".
- To continue w/ the analogy, other tables follow the example of "red ants" to "bugs. So, while I learned about 'fruits', that knowledge doesn't help me w/ the mapping of bugs or else.

In summary, it appears that there may not be a quick/dirty answer to this mapping. If the first round of VBA is not able to mimic the output listed under the green banner, then I think I'm stuck w/ the *manual* (semi-manual using keywords with wildcards) process.

I'm still hopeful that some algorithm can produce what I did manually/semi-manually though.

I welcome your thoughts.

[orange]

Thanks.
If you can describe a tool that uses newValues and searches for matches (of some string or full word) in the oldValues, then assigns a mapping on matches, that would be helpful. At least for an approach.

[skydivetom]

Jack:

1. "If you can describe a tool that uses newValues and searches for matches (of some string or full word)"

a. In my view, it should be the other way around... i.e., take an existing (source) value (out of the 160) and -- based on keyword search -- identify a matching new (target) value (out of the 52).
b. Next, given that some records have keywords in plural (injury vs. injuries), it is best to choose maybe only the first 4 or 5 (or n) characters. For instance, "injur*" would find both "injury" and "injuries".
c. Naturally, based on my "green" example record "abrasion to right index finger" does not utilize the keywords "trauma". Ultimately, based on manual (human-in-the-loop) screening, I made that assignment though.
d. Based on c., it is obvious that the 1st loop/iteration will NOT find 100% matches for all source's value keywords. However, it would be absolutely awesome even if only 50% (hopefully more) could be automatically assigned.

Arguably, the underlying logic is simple.
1. Apply LEFT(5) and pick a keyword from the source record (e.g., "injur") and find a matching record in target record (e.g., "Injury- Other").
2. Then have routine copy/past "Injury- Other" into field "TARGET_VALUE in the LK table.

Challenges where the "simple logic" gets more complicated:
a. Record #60: Fracture of 4th metacarpal, left hand" ... what is the keyword? "fract" OR "metac*" OR "left*" OR "hand*"?
Answer: Maybe the routine can try all 4 keywords separately.

b. Record #71: "Hip injury"... ... what is the keyword? "hip" OR "injur*"?
Answer: Maybe the routine try both keywords separately.

c. Record #71: "Hip injury"... ... using "injur*", which is the correct matching record?
- "Other Traumatic Injuries and Disorders"
- "Injury- Other"
Answer: Maybe, if multiple target values contain "injur*", routine can either utilize the 1st matching target value OR potentially flag COMMENT field indicating both "Other Traumatic Injuries and Disorders" & "Injury- Other" were found.

In a nutshell, this is how I envision the "tool" to work.

[orange]

yes, I hear what you're saying. That's what I was trying to do, but possibly in more steps.
For each of the old values, look for a word or substring that represents the "main meaning/topic", and group the old values.
Then for each of the old group (names) search the new terminology for a match(es). I did the grouping step, but not the assignment of old to new terms.
I agree totally with "what is the "correct" term? That's where refinement and subject area expertise are needed. That's where the general what's in and what's out streamlines the communication/refinement.

I was looking at a more general technique, but don't have time to do more until next week.

[skydivetom]

Jack:

Copy all -- glad to learn we have the same baseline WRT to the procedures.

Totally understand "but don't have time to do more until next week". This is an online forum and I appreciate any help I can get (whenever I can get it)... so, again, copy all!

I myself have to take a break from the manual mapping. I'll return to it Monday morning w/ a fresh mind. There 70+ LK tables left, so even if I could start utilizing an automated routine some time next week, it would be extremely helpful to assist me with the mapping of other source values. Just please keep in mind that any solution should be flexible enough to be applied to any concept.

- Specifically, in the 1st example (posted in this thread), we identified "injuries"... and I referred to it as the "green apple/Granny Smith" to "Apples" scenario.
- Other tables, however, may include job duty source data such as "Worked as electrician on multiple projects"... where keyword "electr" would have to be mapped to target value "Electrician", or ...
- ... source data may include equipment type info such as "Carrying a kevlar bag containing Radioactive Material"... where keyword "radioact" would have to be mapped to "Radioactive", etc. etc.

I hope this helps and provides additional details. Again, the overarching goal is to pick a sub-string of the old (source) value and find that same sub-string in my new (target) values/reference terms, where the latter is much more concise. And, finally, once a term was found, the key is to add the full matching term to the LK table. But I think you already got this down... I'm repeating myself.

Have a great weekend,
Tom

[orange]

Tom,

I don't see any automated mapping. However, I can see a support tool that would "streamline mapping" to some degree.
I also noticed that the LK* tables can have duplicate values. This occurred because I populated my test tables with a table and another copy of the table, and although individual tables were distinct, the populating of LK* was not necessarily distinct values.
So I have modified the LK create routine to insert field called ID to be the primary key. I also put a Unique index on the LK* tables to prevent duplicate values.

The support tool would need some logic to deal with individual LK table and the table with the "new/Standard code". I foresee using a specific LK table and the tbl with standard values, performing the mapping. Then select next LK table and its target values and map that. That is Looping through each of the tables.
Tool would allow search with strings/fragments to identify probable matches, user identifies matches by selecting in a listbox, then clicks a button that update the appropriate fields in appropriate table.

I have a search routine that finds words or parts of words across several tables,I think it could be a base for such a tool.