Compare

Menu: Found in the DATA menu

The Compare command enables the comparison of a primary and a secondary tables to determine whether the two tables are the same and if not, to identify the differences between them. This command is useful for comparison of tables from two different points in time (for example: cheque registers, log files or master files).

The file comparison can be keyed or unkeyed. The most recent version of the data is considered the primary file, while the previous version of the data is considered the secondary file. The context of comparison results is based on this paradigm.

For all comparisons (unkeyed or keyed), the Compare command relies on consistency in the creation of the table’s content. To that end, for both files, field names referenced must be valid for both the primary and secondary file.

When the comparison is keyed, the Compare command also relies upon:

Keyed vs. Unkeyed Comparisons

The benefit of a keyed comparison is that the results are more granular regarding adjacent changed records.

In an unkeyed comparison, adjacent inserted or deleted records could be mistaken as changes.

In a keyed comparison, adjacent changed records would be correctly interpreted.

Note: A key should not be contrived where one does not naturally exist. If the file contains a unique sorted key then you should specify it to get the most accurate results. If the file does not contain such a key then an unkeyed comparison should be used.

Parameters

The Compare command has the following command parameters: Append, If, First, Open, Next, To and While. For a description of these parameters, see Command Parameters. For a description of supported field modifiers see Field Modifiers.

Note: The IF, WHILE, FIRST and NEXT parameters should be used sparingly as they only exclude primary records from the comparison and as a result may cause apparent comparison differences that do not actually exist.

On

Specifies a key field for a keyed comparison. In the command dialog, check the “Unique sorted key” radio button and select the key field from the pull-down list or click [Choose] to display the selected fields dialog in which you can select fields and build expressions. For more information on the Selected Fields dialog, see Selection/Edit Dialog Boxes.

Fields

Specifies the field(s) to compare in an unkeyed comparison, and specifies additional fields to compare for each key value when a keyed comparison is performed. In the command dialog, select the field(s) from the “Fields to compare” list box or click [Choose] to display the selected fields dialog in which you can select fields and build expressions.

Maximum

Suppresses the detailed listing of values when there are more than the specified number of consecutive items. The default is 10 if not specified. This only applies for output to screen as all differences are included in data file output. In the command dialog, click the [More] button and enter the maximum in the “List fields for up to...” text box.

Errorlimit

Used to trigger an error message "Files are not the same" when n lines differ indicating that the two files are essentially dissimilar. If at any time there are n consecutive differences then the error message “Command cancelled” is generated in the Command Log followed by the message that the files are different. The default value is 5000 if not specified. This only applies for output to screen as all differences are included in data file output. In the command dialog, click the [More] button and enter the errorlimit in the “Stop processing after...” text box.

Tip: Whenever the secondary file is empty (i.e. contains no records) or whenever the specified Errorlimit is exceeded, the Compare command creates a WRITEn variable with a value of -1. This enables Compare command results to a data file within a procedure to be easily tested existence (no secondary file records) or for the compared files being significantly different (exceeding reasonable Errorlimit as currently set for the command).

Other

Enables fields aside from the fields being compared to be listed in the command results for records that are identified as having been inserted, deleted or changed. In the command dialog, click the [More] button, select the field(s) from the “Additional output fields” list box or click [Choose] to display the selected fields dialog in which you can select fields and build expressions.

The displayed value of OTHER fields will originate from:

Specifying OTHER fields is useful where you are comparing field values between files for which another field has an expected one-to-one relationship that you want to confirm.

Command Mode Syntax

COMPARE <ON key-field><FIELDS field-list><OTHER field-list>

<MAXIMUM n> <ERRORLIMIT n>

<TO data-file-name|SCREEN> <IF test>

<WHILE test> <FIRST|NEXT range> <APPEND>¿

Note: The field names included in the field list must be valid for both the primary and secondary file.

Examples

The first example illustrates an un-keyed comparison of two monthly expense tables to identify the differences using the Compare command:

Performing an unkeyed comparison of the newer February Expenses table to the prior January Expenses table yields a table listing the Compare command results. The table contains a TYPE and a RECORD column along with the chosen fields.

The TYPE column indicates whether the a record has been added, deleted of changed (from the perspective of the primary table):

The RECORD NUMBER column indicates which relative record has been added, deleted or changed (from the perspective of the primary table).

The second example illustrates a keyed comparison of two monthly expense tables already ordered (sorted) on a key field (Name). The key field is used to identify the differences using the Compare command.

The sorted key field for the comparison is Name and the additional compare field is Balance.

Performing a keyed comparison of the newer sorted February Expenses table to the prior sorted January Expenses table yields a table listing the Compare command results. The table contains a TYPE and a RECORD column along with the chosen fields (Name and Balance).

Note: Whenever a keyed comparison is performed, the key field is expected to contain unique key values. If duplicate key values are encountered, the duplicate values are ignored and only the first unique key value is processed. The Compare command will report the number of duplicate key values skipped when performing a keyed comparison in the Command Log.