Skip to content
English
Contact us
  • There are no suggestions because the search field is empty.

Updating Crime Codes to Contain IBR Values

No recent searches

    Popular Articles

            Sorry! nothing found for

            Updating Crime Codes to Contain IBR Values

            Created by Russ Hartle, Modified on Tue, 11 Nov, 2025 at 10:18 AM by Russ Hartle

            Assumed Assumptions

            • The customer does not have existing IBR codes in their database. My assumption is that we are adding new.

            Obtaining State Codes and Cleaning the Data

            1. Obtain the latest offense table spreadsheet from the state.
            2. Make a copy of the above offense table to have a copy of it in its original formatting.
            3. Rename the copied spreadsheet appending scrubbed to it.
            4. In the “SCRUBBED” spreadsheet perform the following tasks.
            • Convert offense descriptions to UPPER case. The RMS adds codes in only upper case, thus anything in lower case will not be located.
            • Convert IBR offense codes to UPPER case.
            • The state codes should have a unique identifier for the code. If not then you will have to create a unique number for each code. The unique number must be 64 characters or less. My suggestion would be the code section + row number. Add a column to this spreadsheet for unique number.
            • Ensure offenses with multiple IBR offense code relationships have a standard format. The importer tool will import a range of codes containing a hyphen (EX: 11A-11D) and it will import multiple distinct codes as long as they’re separated by a comma (EX: 35A,09A). In my experience with Texas, I found that the majority was in our format, however, there were ones not in this format (ex: 11A/11D, 11Aor11D). My suggestion is to copy the IBR column to a new spreadsheet. Once in the new spreadsheet apply the remove duplicate formula to the column. This will provide you with a list of distinct values in the IBR column. Look through the distinct values and find anyone needing correction and apply the correction.

            Extracting Offense Codes from the Customer Database

            1. Run the following SQL against the customer database. Depending on the fields provided by the state the following SQL might need to be adjusted. This assumes you will be comparing code section, code type, and code description.
              • select i.INCIDENT_CRIME_TYPE_ID, i.CODE_TYPE, i.CODE_SECTION, i.DESCRIPTION from INCIDENT_CRIME_TYPE i where i.DELETED_TS is null
            2. Ensure that the program you use to execute the SQL returns all records. Often times they only return a few hundred unless you specifically tell it to return all.
            3. Export the data obtained above into a CSV file. Ensure that you designate that the output CSV wraps values in quotes. This is crucial in maintaining GUID integrity. Both IBExpert and Flame Robin have the option for quotes.

            Preparing the Customer and State Codes for Comparison

            1. The following directory contains two documents. Create a copy of these documents and place them on your computer.
              • \\cifsserver1\zdrive\Internal Documentation\Data Importer Tool\Import ICTs\Templates\
            2. Rename the file “data-comparison-work-sheet.xlsx” to have the customers name appended to the beginning.
            3. Rename the file “final-data-sheet-for-importer-tool.xls” to have the customers name appended to the beginning.
            4. Open up the “data-comparison-work-sheet.xlsx”
            • Navigate to the sheet titled “Step1 – Load Customers Codes”
              • Import the existing customer codes from the CSV file using the “From Text” import option in Excel.
                • MAKE SURE TO
                  • Select the delimiter as a comma
                  • Set the text qualifier to a double or single quote. You will see the quotation marks around your fields in the data preview. Select the correct quotation.
                  • On step three of the process ensure to set all four of the columns to “text”. This will ensure the data integrity is maintained.
            • With the customer codes imported highlight the imported text and ensure they are formatted as text.
            • Move to the sheet “Step2 – Clean Customer Codes” and place the cursor in cell B1. In cell B1 select the “clean” formula. When prompted which field to clean select A1 from the sheet “Step1 – Load Customers Codes”
              • Drag the clean formula across the appropriate amount of columns. In this example, we imported four fields so I will drag it from B1 – E1.
              • Now drag the formula down for the number of rows that exist in the “Step1 – Load Customers Codes” sheet.
            • Label the A1 column as “Concatenation” in sheet “Step2 – Clean Customer Codes”
            • In Cell A2 select the excel concatenation formula. You will need to concatenate code section, type, and description with a character between them. The character I used was “|”.
              • Drag the concatenation formula down for each row in the sheet.
            • Select all the text in the sheet “Step2 – Clean Customer Codes” and format it as text.
            • Apply a filter to the column headers in the sheet “Step2 – Clean Customer Codes”.
            • With the filter applied. Select the “Concatenation” column and sort it A-Z.
            • Copy and paste the scrubbed state codes into sheet “Step3 – Load State Codes”
            • Select all text in sheet “Step3 – Load State Codes” and format it as text.
            • Move to the sheet “Step4 – Clean State Codes” and place the cursor in cell B1. In cell B1 select the “clean” formula. When prompted which field to clean select A1 from the sheet “Step3 – Load State Codes”
              • Drag the clean formula across the appropriate amount of columns to clean each column from the “Step3 – Load State Codes” sheet.
              • Now drag the formula down for the number of rows that exist in the “Step3 – Load State Codes” sheet.
            • Label the A1 column as “Concatenation” in sheet “Step4 – Clean State Codes”
            • In Cell A2 select the excel concatenation formula. You will need to concatenate code section, type, and description with a character between them. The character I used was “|”. Note if you are comparing on different fields then you will obviously need to adjust the concatenation formula to fit your needs.
              • Drag the concatenation formula down for each row in the sheet.
            • Select all the text in the sheet “Step4 – Clean State Codes” and format it as text.
            • Apply a filter to the column headers in the sheet “Step4 – Clean State Codes”.
            • With the filter applied. Select the “Concatenation” column and sort it A-Z.
            • It goes without saying SAVE SAVE SAVE lol.

            Comparing Customer Offense Codes to New State Codes

            1. Copy all data from the sheet “Step4 – Clean State Codes” and paste it into “Step5 – Compare New to Old”
            2. Select all used columns in the “Step5 – Compare New to Old” sheet and format them as text.
            3. Place the cursor in row 1 (assuming row 1 has column headers) of the last column of the sheet “Step5 – Compare New to Old”.
              • Name this column “Lookup”.
            4. Place the cursor in row 2 of the newly added lookup column
              • Select the “VLOOKUP” formula and use the following criteria.
                • LOOKUP_VALUE would be A2.
                • TABLE_ARRAY would be all columns in the “Step2 – Clean Customer Codes” sheet.
                • Col_index_num – This must be a numeric value. We want vlookup to return the GUID from the “Step2 – Clean Customer Codes” sheet. In order for this to happen you must count the columns left to right and place the numeric location of the “INCIDENT_CRIME_TYPE_ID” field in the “Step2 – Clean Customer Codes” sheet.
                • Range_Lookup should be set to FALSE.
              • Drag the vlookup formula down for each row in the sheet.
              • Select all used columns in sheet “Step5 – Compare New to Old” and ensure the formatting is set to text.
            5. Apply a filter to the column headers in sheet “Step5 – Compare New to Old”
            6. In the “Lookup” column in “Step5 – Compare New to Old” use the filter to EXCLUDE all values except “#N/A”
              • Copy these filtered results into the sheet “New” in the “final-data-sheet-for-importer-tool.xls” spreadsheet.
              • Select all the columns containing text in the “New” tab in the “final-data-sheet-for-importer-tool.xls” and format them as text.
            7. # In the “Lookup” column in “Step5 – Compare New to Old” use the filter to INCLUDE all values except “#N/A”
              • Copy these filtered results into the sheet “Used” in the “final-data-sheet-for-importer-tool.xls” spreadsheet.
              • Select all the columns containing text in the “Used” tab in the “final-data-sheet-for-importer-tool.xls” and format them as text.
            8. Clear all content from the sheet “Step5 – Compare New to Old”
            9. Copy the entire sheet “Step2 – Clean Customer Codes” and paste it into “Step5 – Compare New to Old”
            10. Select all the text in sheet “Step5 – Compare New to Old” and format as text.
            11. Place the cursor in row 1 (assuming row 1 has column headers) of the last column of the sheet “Step5 – Compare New to Old”.
              • Name this column “Lookup”.
            12. Place the cursor in row 2 of the newly added “lookup” column
              • Select the “VLOOKUP” formula and use the following criteria.
                • LOOKUP_VALUE would be A2.
                • TABLE_ARRAY would be all columns in the “Step4 – Clean State Codes” sheet.
                • Col_index_num – This value can be 1.
                • Range_Lookup should be set to FALSE.
              • Drag the vlookup formula down for each row in the sheet.
              • Select all used columns in sheet “Step5 – Compare New to Old” and ensure the formatting is set to text.
            13. Apply a filter to the column headers in sheet “Step5 – Compare New to Old”
            14. In the “Lookup” column in “Step5 – Compare New to Old” use the filter to EXCLUDE all values except “#N/A”
              • Copy these filtered results into the sheet “Unknown” in the “final-data-sheet-for-importer-tool.xls” spreadsheet.
              • Select all the columns containing text in the “Unknown” tab in the “final-data-sheet-for-importer-tool.xls” and format them as text.
            15. In the “Lookup” column in “Step5 – Compare New to Old” remove the previously applied filter so that all data displays.
            16. In the concatenation column in “Step5 – Compare New to Old” apply conditional formatting to highlight duplicates in a color of your choice.
              • Filter the concatenation column by color so that only duplicate values exist.
              • Copy and paste these duplicated values into the “Duplicates” sheet in the “final-data-sheet-for-importer-tool.xls” spreadsheet.

            Customer Verification

            • At this point, it is CRITICAL that the customer is advised of the situation. Send them the document “final-data-sheet-for-importer-tool.xls” and advise them that the new ones will be added, the updated ones will be updated to include the IBR value, and the unknown will be concealed from the picker. Explain to them how picker conceal option works. At the point, I’m writing this 12/19/18 we are in the process of creating a filter mechanism. It will be included in the patch for PA IBRS.

            Finalizing the Import Data Sheet

            1. First and foremost I assume that the customer has approved the standard process primarily the approval of concealing the unused codes.
            2. In the sheet “New” within the “final-data-sheet-for-importer-tool.xls” spreadsheet add the following columns and data. Make sure to add the newly added data values to all rows in this sheet.
              • Applicable State – Value: This is the state abbreviation of the agency. Enter it in caps.
              • Picker Conceal – Value: F
              • Jurisdiction Level – Value: S
            3. Ensure all columns in the “New” sheet are formatted as text.
            4. In the sheet “Update” within the “final-data-sheet-for-importer-tool.xls” spreadsheet add the column “ID_IS_GUID” and set the value to “Y” for every record in this sheet.
            5. Ensure all columns in the “Update” sheet are formatted as text.
            6. In the sheet “Unknown” within the “final-data-sheet-for-importer-tool.xls” spreadsheet add the following columns and data. Make sure to add the newly added data values to all rows in this sheet.
              • PICKER_CONCEAL – Value: T
              • ID_IS GUID – Value: Y
            7. Ensure all columns in the “Unknown” sheet are formatted as text.
            8. At this point I want to take a moment and discuss the “duplicates” sheet in the “final-data-sheet-for-importer-tool.xls” spreadsheet. This sheet contains duplicates in the customer data. When the vlookup process does a lookup up for a match it will find the first match. This means that the duplicate value will not receive an IBR value. What I did was provide these to the customer at the end and explain that they were duplicates and they could go through and consolidate them into one. I would recommend after the import they fix them. Fixing them in the process of assembling this data will potentially cause our import to miss both duplicates.

            Importing The Data

            The usage of the data importer tool will not be discussed in this topic. It will be discussed in another and/or illustrated in a video. What I want to discuss is what fields should be imported on the “new”, “update”, and “unknown” sheets in the ““final-data-sheet-for-importer-tool.xls” worksheet.

            1. For NEW crime codes the following fields must be imported.
              • UNIQUE CODE IDENTIFIER, DESCRIPTION, CODE_SECTION, CODE_TYPE, APPLICABLE_STATE, PICKER_CONCEALED, IBR VALUE, JURISDICTION LEVEL
            2. For UPDATED crime codes the following fields must be imported.
              • LOOKUP VALUE (THIS IS A GUID), IBR VALUE, ID_IS_GUID
            3. For UNKNOWN crime codes the following fields must be imported.
              • LOOKUP VALUE (THIS IS A GUID), ID_IS_GUID, PICKER_CONCEALED

            Testing The Process

            • Testing this is essential. The easiest way to test for me was to generate the import list and import them into my test customer database. At this point, I extracted all the codes from the customer database and ran them through the test again. What you should see is that all the new state codes find a match via vlookup in the existing customer codes.

            Was this article helpful?

            That’s Great!

            Thank you for your feedback

            Sorry! We couldn't be helpful

            Thank you for your feedback

            Let us know how can we improve this article!

            Feedback sent

            We appreciate your effort and will try to fix the article

            X