Training Module 6 :Record Filtering

The slides for this training video are shown below:

Slide 1

Welcome to the training course on IBM Scalable Architecture for Financial Reporting, or SAFR. This is module 6: “Record Filtering.”

Slide 2

This module provides you with an overview of SAFR record filtering. Upon completion of this module, you should be able to:

Use SAFR logic text facilities to create record filters
Read SAFR logic text and identify keywords, fields, lookup paths, and constants
Construct complex record filters
Interpret the results of processing

Slide 3

SAFR record filters use conditional processing to include or exclude records, based upon specific criteria. SAFR filters both input records and output records. Input record filtering is performed in the extract phase, and output record filtering is performed in the format phase.

Both input and output record filtering work on entire records. If no record filters are created, all records are selected for input, and are written to the output.

Record filters are written in SAFR logic text.

Slide 4

In contrast to record filtering, SAFR also provides column assignment logic and column calculations, which also use SAFR logic text. Column logic is used to populate output columns with field values, constants, or calculations.

In the top example, record filtering excluded all customers with orders before January 2000. In the bottom example, columns are populated based upon customer type, either retail or commercial. Note that no value is assigned for the third record because it is for a “government” customer.

Slide 5

The extract record filter is opened on the View Source Properties tab. First, open the View Source Properties tab with a single left-click in the blue cell. Open the Extract Record Filter tab by double-clicking anywhere in the record filter cell to display the icon, and then click the icon.

Slide 6

The Format phase record filter is opened on the Format Phase tab on the View Properties screen. Simply click the Edit button to work with output record filters.

Slide 7

SAFR logic text is constructed in the Logic Text Editor. The left panel displays the logic, and the right panel displays the Logic Text Helper, which displays key-words and values that can be used in logic text. Within logic text, comments are preceded by an apostrophe, and are shown in green. Keywords are shown in blue. Field names are shown in black, and alphanumeric constants are shown in pink, enclosed in pink double quotes.

Slide 8

Because the Extract process has access to the original input record, extract record filters use field names and LR names. In SAFR meta-data, for example, field names are enclosed in brackets. Format phase logic text filters refer only to column numbers, not to field names.

Slide 9

The logic text helper enables you to build logic text by selecting the text to include in the filter. Double-clicking any listed keyword or field places that value in the logic text. Constants, such as the pink zeros shown here, are the only portion of the logic text that requires manual entry.

Slide 10

The Logic Text Helper includes operators, such as these:

Logical operators (AND, OR, NOT)
Arithmetic operators
Comparisons such as GREATER THAN or LESS THAN, and
Keywords, such as LIKE and CONTAINS.

Slide 11

The SELECT and SKIP key words tell the Performance Engine which record to select or skip. The logic shown in this example selects all records with an order date greater than January 1^, 2000. All other records will be skipped.

Slide 12

The SELECTIF and SKIPIF statements provide a shorthand way of specifying the same condition. Note that the greater-than sign has been switched to a less-than-or-equal-to sign, so that both statements will select the same records.

Slide 13

The Logic Test Helper includes a listing of all fields from the input logical record. To avoid syntax errors, place the cursor at the correct location in the SAFR keyword, and double-click to insert the selected field name.

Slide 14

The Logic Text Helper also shows all lookup paths and fields on LRs available through joins. When used in logic text, these field names are preceded by the lookup path name.

In this example, the logic tests the customer date-of-birth field, looking for records with specific dates to include or exclude.

Slide 15

Format filtering can be performed only on records that have made it through record selection, in the Extract phase. Format phase output record filtering refers to columns rather than field numbers. The only columns available for output filtering are columns that are numeric, perform aggregation, or are the results of calculations. Format filtering is applied after aggregation is performed.

In this example, only records where column 6 is greater than 0 are written to the final output file. The accumulated order count in column 6 for Johnson is 0, so this record is not written to the output file, but the Philips record, with a total of 3, is.

Slide 16

Logic text functions test numerous conditions, including various date conditions, nulls, numeric values, and found and not-found conditions on joins. In this example, all records with spaces in the ORDER DATE field will be skipped.

Slide 17

Logic text supports nested IF statements. SAFR logic text follows the typical order of operations and logic constructs of other programming languages.

In this example, all order records for customer 0 at stores 1 and 2 are selected. In addition, all orders for customer 2 at any store, with an amount less than or equal to 0, are selected for processing.

Slide 18

The ISFOUND (is found) and ISNOTFOUND (is not found) functions test the results of joins. If a corresponding join record is found, the ISFOUND condition tests true. The parameter for these functions is simply the lookup path to be tested for a valid join.

In this example, order records are selected if a customer record is found using the order-to-customer lookup path.

Slide 19

To view a quick syntax snapshot, hover over the Logic Text Helper keywords. SAFR online help provides full syntax help for all keywords. To access help, open the Help menu and, select Logic Text.

Slide 20

Logic text syntax checking tests logic text for errors, such as mistyped keywords or invalid field names. Error messages are displayed at the bottom of the screen. Views cannot be activated until all errors are cleared.

Slide 21

Logic text syntax checking displays errors when syntax is not correct. The error message displays the line followed by the character number where the error was detected.

In this example, the syntax is missing a second right parenthesis after the constant “2.”

Slide 22