A common scenario in the eDiscovery world is a need to switch first names and surnames around – or simply align them to the common alias value.
“Cassinelli, Jed” ≠ “Jed Cassinelli.", right? Technically, they are indicative of the same person / entity, but the presentation of the data is always part of the equation.
So if you've already normalized your data, and the client's requirements for displaying the names change, you might find yourself repeatedly saying: "Last, First. No, First Last. Wait, they changed it again?? Oh goody."
Chronicle gives you tools for specifying normalized values - setting them in a one-off capacity, uploading them in bulk – enabling a solution for this task sourced from your existing Name Normalization work product.
Within the UI, there is no immediate means of making this sort of wholesale change (yet). So what can be done to switch names when, for example, that 2nd request - with all of its myriad names - has a requirements change on the display of names? Enter our superhero, Excel! (trusty sidekick to Chronicle!)
Reference materials: Here's a sample spreadsheet with the formulas I'll discuss below.
Instructions
(1) First, pull bad data out using Chronicle
Navigate to the Manage Normalizations tab. Here, we can see all the normalizations in this workspace.
Filter (if needed) the results to those that you want to change
Select 'Export all data to CSV' to get a list of Original Values and their Normalized Values.
Download and open the CSV.
(2) Then, use Excel (formulas) to do the heavy lifting.
We'll add columns and formulas to the CSV to calculate the new values.
These formulas are written assuming you are starting with a CSV with the following columns, including headers in row 1:
Original Value (A)
Normalized Value (B)
Occurrences (C)
Documents (D)
You won't need columns A, C, or D, but we'll assume you're leaving those in the right place.
If you are converting from "First Last" to "Last, First:
In column E, pull out the first name with this formula:
=LEFT(B2, SEARCH("", B2,1)-1)
b. In column F, pull out the last name with this formula:
=TRIM(RIGHT(B2,LEN(B2) - SEARCH("", B2, SEARCH("", B2))))
c. In column G, put the names back together with this formula:
=CONCATENATE(F2, ", ", E2)Note: Columns E & F are not required, and you could shortcut directly to the following formula:
=CONCATENATE(TRIM(RIGHT(B2,LEN(B2) - SEARCH("", B2, SEARCH("", B2)))), ", ", LEFT(B2, SEARCH("", B2,1)-1))If you are converting from Last, First to First Last:
In column E, pull out the last name with this formula:
=LEFT(B2, SEARCH(",", B2,1)-1)
b. In column F, pull out the first name with this formula:
=TRIM(RIGHT(B2,LEN(B2) - SEARCH(",", B2, SEARCH(",", B2))))
c. In column G, put the names back together with this formula:
=CONCATENATE(F2," ", E2)Note: Columns E & F are not required, and you could shortcut directly to the following formula:
=CONCATENATE(TRIM(RIGHT(B2,LEN(B2) - SEARCH(",", B2, SEARCH(",", B2))))," ", LEFT(B2, SEARCH(",", B2,1)-1))(3) Do a little QC on the new values.
Before moving on to getting this data back into Relativity, one quick note: These formulas DO NOT cover all edge cases!
For a couple of the known issues, we can add another formula to warn us if this might be causing a problem with the new values being calculated.
When going from "Last, First" to "First Last" - If there is more than one comma in a value, it won't be correctly converted (e.g., "Cassinelli, Esq., Jed" → "Esq., Jed Cassinelli ")
In column H, check to see if the starting value has more than one comma with this formula: =(LEN(B2)-LEN(SUBSTITUTE(B2, ",",""))>1)
When going from First Last to Last, First - If a value is in the form First Middle Last, the middle portion will be incorrectly treated as part of the last name (e.g., "Jedidiah C. Cassinelli" → "C. Cassinelli, Jedidiah ")
In column H, check to see if there are more than two names with this formula: =(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>1)
If the value is TRUE for any of the rows with these QC formulas, it's probably worth examining the calculation to see if it makes sense before using it.
(4) And now put the (good) data back into Chronicle.
Once all the new values have been calculated, we must get them back into Chronicle.
Copy the good values from column G into column B, overwriting the bad values. (G=good, B=bad )
Delete the extra columns added—where we're going, we don't need extra columns. This is optional, but who doesn't like a fresh start?
Navigate to the Manage Normalizations tab.
Select Import and select your updated file to load. This will update all the normalized values within Chronicle.
Navigate to the Normalization Projects tab, where you should see an indicator of projects that have been normalized and were just updated.
For each project, navigate to the Apply Normalizations tab and click Write values to Fields to push these updated values into their associated Relativity Fields.
Comments