The Compare Databases task lets you identify differences in a single Numeric field within two databases (referred to as the primary and secondary databases), for a specified common match key. The databases being compared need to be in the same project folder. The general use of Compare Databases is to compare the same database at two points in time, such as the payroll at the beginning and end of the month to identify changes in salary for each employee (the match key). Neither the match key fields, nor the fields for comparison need to have the same names in the two databases, but the match key fields must be of identical type. As there may be more than one record for each value in the match key in either or both of the databases, the Compare Databases task compares the totals for the Numeric field in each database. Since it first summarizes then compares the databases, it identifies how many records there are in each database for each match key field.
Before beginning the Compare Databases task, ensure that the primary database is active.
1. On the Analysis tab, in the Relate group, click Compare to open the Compare Databases dialog box. Specify the Numeric field in the primary database to be compared by clicking on the.
2. Click Select to select the secondary database
3. Specify the Numeric field in the secondary database to be compared by clicking on the.
4. Click Match to specify the match key which is the field or fields by The output database is created as a child to the active database and includes the following fields in addition to the field(s) which make up the key:
Field Anchor Size
Use the keyboard arrow keys to reposition and size your fields.
Returns the numeric value of a specified field statistic.
FieldName – The field that will be analyzed for the specified field statistic. The field name must be entered within quotation marks. Note
Lowercase characters are accepted. For example, a valid field name parameter can be entered as "AMOUNT", "Amount", or "amount". Additionally, extra spaces are accepted as IDEA will remove them before processing the equation. For example, "AMOUNT" and " AMOUNT " are accepted.
Statistic – The assigned number for the required field statistic. See Assigned Numbers for Field Statistics.
In the following example, the Virtual field equation is calling field statistic "2", which is the assigned number for the field statistic Number of zero items. Looking at the data in the TOTAL_AMT field, there are four records that contain a zero value. As a result, each record in the Virtual field will contain the value 4 to represent that there are four records in the database that contain zero values.
• Do not perform field statistics on Virtual fields that contain the @FieldStatistics function.
Due to how field statistics are generated, field statistics on Virtual fields will not be accurate if the equation in the Virtual field contains the @FieldStatistics function.
• Regardless of the field statistic type, @FieldStatistics returns a numeric value, as a result:
• @FieldStatistics will generate "Error" in the database when: • Field statistics have not been calculated on the database.
When creating an Action Field link to display an Extraction Preview window, you must make the following choices.
To create an Action Field link:
1. In the Define Action Fields dialog box, select the Create Action Link to display extraction preview option.
2. Optionally, to find matches in a different (secondary) database, click the Browse button. The Select Database dialog box opens. Navigate to and select the required database. The two databases must be in the same project. Additionally, the two databases must contain field(s) with the same field names and type in order to compare them for values that match.
3. Optionally, to select a different field(s) as the look-up field, click the Look-up Fields button. Select the field(s) from the list of common fields. When you select multiple fields, the cursor in the resulting Action Field link has a plus sign beside it to indicate that the link consists of multiple fields. The maximum number of common fields that can make up the look-up key is eight.
THINK OUTSIDE THE BOX
Regular expressions enable pattern matching of data.
String1 – A Character field or value to which you want to apply a pattern or formula.
String2 – The pattern that you want to match String1 against.
Example: (to determine if email addresses are structured correctly:
Regular expressions (RE) can be constructed based on the following rules.
• Rules for one-character REs (matching a single character)
• Rules to build a multi-character RE
A one-character RE followed by a plus (+) matches one or more occurrences of the RE. Hence, "[a-z]+" matches one or more lower-case characters.
A question mark (?) is an optional element. The preceding RE can occur zero or once in a string. Example: "xy?z" matches either "xyz" or "xz"
The concatenation of REs is a RE that matches the corresponding concatenation of strings. Example: "[A-Z][a-z]*" matches any capitalized word.
The OR character ( | ) allows a choice between two regular expressions. For example, "ST(. | REET)" matches either "ST." or "STREET"
If the caret (^) is at the beginning of the (sub)expression, then the matched string must be at the beginning of the string being searched.
If the dollar sign ($) is at the end of the (sub)expression, then the matched string must be at the end of the string being searched.