Filter features to identify discrepancies in ArcGIS Pro

Summary

In ArcGIS Pro, data can be filtered with a definition query to identify when values in one field are not present in another field. This process aids in data completeness, consistency and accuracy by automating the identification of discrepancies, which is especially useful for large datasets.

This article describes the workflow to filter features where a field does not contain values from another field using a definition query in ArcGIS Pro. In this example, some adresses are missing the corresponding value from the AddressNumber field.

Procedure

1. Open the ArcGIS Pro project.
2. In the Contents pane, right-click the layer and select Properties. In the Layer Properties dialog box, select Definition Query.

Note:
Alternatively, on the ribbon, click the Data tab. In the Definition Query group, click Build Definition Query .

3. Click the New definition query drop-down menu and select Create a new definition query in SQL.

4. In the query builder, specify the following SQL expression. In this expression, '%' represents wildcards that allow any character to appear before and after the value in <field 2>. The || operator concatenates the wildcards with <field 2>.

<field 1> NOT LIKE '%' || <field 2> || '%'

Note:
Both fields must have similar field types.

5. Click Verify the SQL expression is valid to verify the query.
6. In the query builder, click Apply.
7. Click OK to close the Layer Properties dialog box.

The table shows the filtered features in the Address field do not contain the values of the AddressNumber field.