Cross-Calculate

 

Next Steps
Overview Aggregate Cross-Calculate Custom Transforms Formats/Keys Lookups Match/Join Pivot/Unpivot Re-Map/Reformat Scrub/Cleanse Select/Filter Sort/Merge Substrings Type-Convert

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 BI, database, or data integration (ETL) platforms.

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


The SortCL program in the IRI CoSort package and IRI Voracity platform supports value calculation between or within columns (fields).

SortCL users derive new values from mathematical equations in field statements. 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 functions, as well as absolute value, modulo, and so on.

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 formulas as needed. Use parentheses to control operator precedence, and create temporary fields to hold intermediate values.

Billing and Reporting Applications


SortCL's cross-calculation features are particularly useful for ad hoc financial equations or spreadsheet-style presentations.

Use cross-calcs to create useful business intelligence in structured report formats with join operations. For example, a SortCL report can determine and display a new account balance during a join of a transaction file to an archive file.

The CoSort GUI in the IRI Workbench contains an 'Expression Builder.' This dialog helps you construct and validate the expression logic you need to define.

Share this page

Request More Information

Live Chat

* indicates a required field.
IRI does NOT share your information.