Best Kept Secrets of IDEA

Experts Weigh In on Their Favorite Tasks and Tricks for Using IDEA

Compare Databases

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.

Step-by-Step

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:

• P_NRECS: Number of records for the match key field(s) in the primary database.
• P_TOTAL: Total of the selected Numeric field values for the match key field(s) in the primary database.
• S_NRECS: Number of records for the match key field(s) in the secondary database.
• S_TOTAL: Total of the selected Numeric field values the match key field(s) in the secondary database.
• DIFFERENCE: P_TOTAL minus S_TOTAL

Importing

Field Anchor Size

Use the keyboard arrow keys to reposition and size your fields.

• Up arrow – increase width
• Down arrow – decrease width
• Left arrow – move to the left
• Right arrow – move to the left

Query Building

@FieldStatistics

Returns the numeric value of a specified field statistic.

Syntax

@FieldStatistics("FieldName", Statistic)

Parameters

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:

• Earliest Date and Latest Date return a Julian date (a date as a numeric value).
• Most Common Day returns 1 for Sunday, 2 for Monday and so on. Most Common Month returns 1 for January, 2 for February and so on.
• Average Time, Latest Time, and Earliest Time return a time value converted to a number of seconds.

• @FieldStatistics will generate "Error" in the database when: • Field statistics have not been calculated on the database.

• The FieldName parameter is not enclosed in quotation marks.
• The FieldName parameter type and the Statistic parameter type do not match.
• The FieldName parameter is invalid (for instance, the field name is misspelled or does not exist in the current database).
• The Statistic parameter is invalid (for instance, not a recognized assigned number).

Convenient

Action Fields

When creating an Action Field link to display an Extraction Preview window, you must make the following choices.

• The field to use as the Action Field. This field converts into a link that displays the Extraction Preview window.
• The database to find matching values in. This can be the same database that the Action Field is in (default) or another one.
• The look-up field(s) for the extraction. The field used for the Action Field is the default, but you can change it. The look-up field must be common to both databases and have the same name.

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

@RegExpr

Regular expressions enable pattern matching of data.

Syntax

@RegExpr(String1, String2)

Parameters

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:

@RegExpr(ADDRESS2, "^([a-zA-Z0-9_-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.)|(([a-zA-Z0-9-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(]?)$")

Regular expressions (RE) can be constructed based on the following rules.

• Rules for one-character REs (matching a single character)

1. Any character that is not a special character matches itself.
2. A backslash () followed by any special character matches the literal character itself; that is, this "escapes" the special character. Such as + * ? . { } ^ $ ( ) [ ] |
3. The period or stop " . " matches any character. For example: ".umpty" matches either "Humpty" or "Dumpty"
4. A set of characters enclosed in brackets ([ ]) is a one-character RE that matches any of the characters in that set. For example: "[akm]" matches either an "a", "k", or "m". A range of characters can be indicated with a dash. For example, "[a-z]" matches any lower-case letter. However, if the first character of the set is the caret (^), then the RE matches any character except those in the set. It does not match the empty string. For example: "[^akm]" matches any character except "a", "k" or "m". The caret loses its special meaning if it is not the first character of the set.

• Rules to build a multi-character RE

1. Parentheses (( )) groups parts of the regular expressions together into subexpressions that can be treated as a single unit. For example, "(ha)+" matches one or more "ha" 's
2. A one-character RE followed by an asterisk (*) matches zero or more occurrences of the RE. Hence, "[a-z]*" matches zero or more lower-case characters.

3. 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.

4. 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"

5. The concatenation of REs is a RE that matches the corresponding concatenation of strings. Example: "[A-Z][a-z]*" matches any capitalized word.

6. The OR character ( | ) allows a choice between two regular expressions. For example, "ST(. | REET)" matches either "ST." or "STREET"

7. 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.

8. 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.