KB1012

Identify duplicate records in an import using the temp table

Summary

If an import fails due to duplicate records in the source data, the duplicate records must be identified and addressed in order to run the import successfully. This article provides some universal guidance on how to identify those duplicates by reviewing the import’s temp table. The process to actually resolve the duplicates varies from import to import, depending on the nature of the source data and the configuration of the import, and is beyond the scope of this article.

Details

Tables in Axiom Budgeting & Performance Reporting can only have one record per unique combination of the table’s key columns. Import utilities insert new records or update existing records based on these keys. If multiple records share the same key values, then by default, the import attempts to aggregate these records into a single row.

To perform this aggregation, numeric data columns are summed. However, certain column data types cannot be summed, such as strings (text), Booleans (true/false), and dates. If the records with duplicate keys have different string, date, or Boolean values, the import will fail with an error such as the following:

Example import error when source data contains duplicate key values

NOTE: Even though the error specifies a row number, that row number is in reference to the import temp table and not the source file, so it may not coincide with the row number in the source file.

Additionally, in some cases imports do not allow aggregation of duplicate key values—such as when the destination table for the import is a reference table, or when the option to aggregate values is disabled. If an import does not allow aggregation, then duplicate values cannot be present in the source data at all, even if it is possible to successfully aggregate the values. The option Aggregate rows on final save on the General tab determines whether aggregation is allowed when importing to a data table.

Import configured to allow aggregation of duplicate keys

When imports are executed, data is read from the source and then placed in a temporary table called the temp table. The temp table data can be validated and transformed before the final save from the temp table to the destination table. In order to troubleshoot import data, you can run an import in development mode, and pause the import after certain transform steps to view the data as it exists in the temp table.

Resolution

In order to run the import successfully, the duplicate rows in the source data must be identified and addressed. You can view the temp table data for the import to assist in this process.

NOTE: In order to run the import in development mode and view the temp table data, you must have read/write permission to the import, as well as execute permission.

Before you begin

In order to evaluate the source data, you must know the destination table for the import and its key columns, as well as the import mappings for those key columns. If you already know this information, skip to the next set of steps.

To identify the destination table for the import, check the Mapping tab.
To identify the key columns for the destination table, open the table using Tables > TableName > Open in Spreadsheet and note the columns with the blue background. Access to this feature requires a certain level of table administration permissions.

TIP: Alternatively, you can view the key columns on the Mapping tab of the import, by clicking the down arrow on the Destination Column list. The key columns of the table have a yellow key before their name. When opening and closing the Destination Column list, take care not to accidentally change the destination column assignment.
Return to the Mapping tab of the import and identify which temp table columns are mapped to the destination table’s key columns. Typically the temp table column names will match the destination table column names, but not always. In the following example, both key columns are in the top section of the Mapping tab, but they could appear in the Work column mappings at the bottom of the tab instead. Make a note of these temp table column names.

Identify duplicate records

Once the key column mappings have been identified, you can review the temp table data to identify the duplicate records.

Run the import again so that you can review the error message when it fails. Make a note of the row number listed in the Execution log. This is the row number that you want to look at in the temp table.
On the Transforms tab, select the Pause check box for the last transform step that is not a Pre-Save Validate transform.
On the Execute tab, select Execute in development mode and then select Pause after specified transforms and display current temp table data. Development mode is a troubleshooting tool that runs the import without actually saving the data. The pause option will cause the import to pause after performing the transform step, so that you can view the temp table data.

NOTE: In Axiom Budgeting & Performance Reporting versions older than 2019.1, the options to enable are named Allow pauses and Preview only.
Execute the import again, and now a View Data dialog will open when the import reaches the specified pause point. This dialog shows the current temp table data for the import. By default, the dialog only shows the first 500 rows of the temp table.
To view the problem row identified in the import error message, you can filter the dialog by the Axreference column in the temp table. This is the column that holds the row numbers for each row in the temp table. In this example, the error was found on row 86 so the filter is Axreference=86.

Click Show Filter in the top right corner to expose the filter options, then in the Filter box, enter the Axreference filter for your error row. Click Refresh to filter the dialog to show that row.
View the problem row in the dialog, and note the key column values for that row. Because this row was identified as a duplicate in the import error, this means that there is at least one more row in the temp table that has the same key column values. To find the duplicate row, clear out the Axreference filter and replace it with a filter for the key column values, then click Refresh again. In this example, the key column values are Dept=109000 and EmpID=1001.
The View Data dialog now shows the duplicate rows for the specified key values. Review any string, Boolean, or date value columns to determine why these rows could not be aggregated. In the previous example screenshot, the string values “Travis Morse” and “Travis Morris” do not match. These duplicate values must be resolved before the import can be run successfully.

The appropriate resolution varies depending on what is wrong with the duplicate data. In this example, it could be that either Morse or Morris is a typo, and the typo simply needs to be corrected in the source file or system. Or maybe a transform step in the import assigned the wrong Dept or EmpID to one of the rows, based on some incorrect table values that need to be fixed. If you have reviewed the duplicate data carefully and cannot determine the root cause of the issue, please contact Axiom Support for assistance.

Outcome

Once the duplicate rows in the source data have been resolved, the import will complete successfully.

Category	Imports
Applies To	All versions
Tags	Duplicate Keys, Save Error, Temp Table, Development Mode
Issue Number(s)	N/A