Data Cross-Calculation
Cross-Calculation Techniques and Logical Expressions
Challenges
During data transformation and reporting, it may be necessary to calculate and derive new field values through mathematical and similar operations. Cross-calculation analysis of data is typically performed in spreadsheets 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 formulas are applied to aggregate values, or when values need to be derived from multiple levels of nested or dependent preliminary values.
Solutions
The SortCL Program sorting program IRI CoSort and the ETL and data management platform IRI Voracity supports cross-calculation of values between or within columns (fields).
SortCL users derive new values from mathematical equations into field statements. These expressions can include their own values, values from one or more existing data fields, or a combination of both.
SortCL's cross-calculation methods include horizontal arithmetic functions, trigonometric, Bessel, and logarithmic functions, as well as absolute value, modulo, etc.
You can also define complex and mixed field and constant expressions, such as:
/FIELD=(NewField=FieldA+400*(32000-FieldC))
and use the ranking 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 functions are particularly useful for ad-hoc financial equations or spreadsheet presentations.
Use cross-calculation techniques 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 when joining a transaction file with an archive file.
The CoSort GUI in the IRI Workbench includes an „Expression Builder.“ This dialog helps you construct and validate the expression logic you are defining.
Related solutions