AX2087

Checking for unmatched data in an Axiom query

In some cases, an Axiom query may return data records from the database that are not placed anywhere on the sheet. This "orphan" data is known as unmatched data (or non-matched data).

Unmatched data may occur in the following circumstances:

• The data ranges for the Axiom query have filters defined, and the record does not match any of the filters.
• The Axiom query is set to update only, and there are no matching keys for the record in any of the data ranges.

In both cases the data record has nowhere to be placed on the sheet, so nothing is done with the record.

It is a good idea to know if your Axiom query has unmatched data. Too much unmatched data can affect query performance—for example, if the query settings return 5,000 records from the database, but only 200 of those records are brought into the sheet. Assuming that you really only want those 200 records, it is recommended to define a data filter on the query (or as a sheet filter) so that the overall data returned is closer to (or exactly matches) the set of data you want to place in the sheet.

You may also want to check for unmatched data if your intent is to use all records returned by the query, to make sure the sheet is set up correctly. For example, plan files often have several data ranges with filters, to organize the sheet into "blocks" by account type. In this case you may want to check for unmatched data, to ensure that the filters on your data ranges are configured correctly and encompass all of the accounts that you want to bring into the sheet.

To check for unmatched data, you can use the QA Diagnostics tool, or you can use a special data range tag to bring the unmatched data into the sheet. For more information on using QA Diagnostics, see Using file diagnostics for troubleshooting and optimization.

Using the UnmatchedData tag

To use the UnmatchedData tag, create a data range tag as follows:

The UnmatchedData tag can be used with either vertical queries or horizontal queries (the above example is for a vertical query).

You can use the data range wizard to insert an UnmatchedData tag. To do this, right-click a cell and select Axiom Wizards > Insert Axiom Query Unmatched Data Range > AxiomQueryName. This will place the tag in the appropriate insert control column or row. You can also double-click an existing tag to edit it and then select Unmatched data range.

When the query is run, any data records that were returned by the database query but could not be placed in the other data range(s) are placed in the UnmatchedData range.

The UnmatchedData range is a special range intended for testing and troubleshooting only. The UnmatchedData range is always rebuilt and inserts all unmatched data, regardless of the refresh type of the query. For example, if the query is set to update only, all of the regular data ranges will honor the update behavior, but the UnmatchedData range will rebuild and display all records that were not found in the regular data ranges.

AX2087