Duplicate case number found in dataset
Crash Magic will generate an error when rendering a diagram that indicates there is a duplicate case number. This is a little misleading because the program is not capable of importing a duplicate record into the database. In reality, this error more than likely means that there is an error in a crash record that is causing our query to generate multiple rows for a single crash. A common example is when a crash has two drivers for the same vehicle.
- Locate the study query, usually called ‘Crashes’ on the Crash Magic administration form.
- Select the blue tab on the Crashes query panel. At the bottom of the panel there is a box for entering query criteria. In that box, enter an expression to select just the crash that generated the error. (i.e. CaseNumber = ‘20181048629’) Then press the “Execute current query” button just to the right.
- If the query generates more than one row, it’s time to figure out which row in the data is causing the problem. Most of the values in the two rows will match. However, some will not. Typically the the rows that don’t match will be vehicle or person fields. Identify the non-matching field names.
- Now that you’ve identified the fields, find out which table that data is coming from. Do this by scrolling up or searching for one of those field names. (e.g. InjuredAge_U2_P1) Note the table “alias” at the start of the field name. In this case, it is D2i.
- Scroll down to find this table alias in the ‘JOIN’ statement below. The example JOIN list here is unusually large. Most configurations have 3 or 4 tables joined at most. What we’re looking for is the name of the table. In this case it is dbo.ZINJ. You can also identify how that table is joined.
LEFT OUTER JOIN dbo.ZINJ D2i ON ((D2i.CrashKey=C.CrashKey) AND (D2i.UnitNum=2) AND (D2i.Seating=1)))
From experience, I will be expecting to find two ZINJ rows for this crash with UnitNum=2 and Seating=1.
- At this point, you must locate the source data for this record. This may be a CSV or XML file from your state or your local PD. You may also have access to this data in an in-house database. Query the ZINJ table for all rows with the matching CrashKey. In this case, indeed it was found that there were two Injury records for unit 2 and seating position 1.
- Correct the offending record and re-export / re-import the crash.