Innovative Routines International (IRI), Inc., The CoSort Company

Cross-Row Math Functions and Logical Expressions

Challenges:

During data transformation and reporting, it may be necessary to cross-calculate and derive new field values through mathematical and similar operations. This functionality is readily available in spreadsheet applications, but is not always easy to accomplish in high-volume data processing tools or operations. Getting the right results can also be a complex matter when applying formulas to aggregate values, or when values must be derived from several levels of nested, or dependent prior values.

Solutions:

For sorting and reporting jobs that require custom financial calculations or spreadsheet-style presentations, the CoSort package's SortCL tool allows you to derive new output values by using mathematical equations in place of a field name. These expressions can include your own values, values from one or more existing data fields, or a combination of both.

SortCL supports horizontal arithmetic functions, plus trigonometric, Bessel, and logarithmic function, as well as absolute value, modulo, and so on. You can define complex and mixed field and constant expressions, such as:

/FIELD=(NewField=FieldA+400*(32000-FieldC)

and leverage the order of precedence to create nested logic and formulae as needed. You can use parentheses to control operator precedence, and create temporary fields to hold intermediate values.

SortCL's cross-calculation features are particularly useful for ad hoc financial equations or spreadsheet-style presentations. You can also usee cross-calcs to create useful business intelligence in structured report formats when used in join operations. For example, a SortCL report can determine and display a new account balance during a join of a transaction file an archive file.