What are Calculated Fields?

When exporting a dataset to some destination, we usually imagine it as

SourceTable.Field1 -> TargetTable.Field1
SourceTable.Field2 -> TargetTable.Field2
...
SourceTable.FieldN -> TargetTable.FieldN

But what if we need to export additional columns: not columns representing physical fields from the source dataset, but results of some ad-hoc calculation? For example, getting a sum of several columns, some dates manipulation, or even extracting data from a local file. The common name for these additional fields is calculated fields, and Exportizer Pro and Exportizer Enterprise allow to define and export such fields.

Exporting Calculated Fields

The key point here is to define the calculated fields (i.e., write the corresponding formulas) on the Field Mappings stage of your data export process.

Of course, if you has SQL knowledge, you can do some calculations on the SQL level:

SELECT 
  ORDER_ID,
  ORDER_DATE,
  ORDER_SUM,
  ORDER_VAT,
  ORDER_SUM + ORDER_VAT as ORDER_TOTAL,
  TRUNC(SYSDATE) as TS
FROM my_table

In this SQL query, ORDER_TOTAL and TRUNC(SYSDATE) are calculated fields, and, of course, you can use such an approach. Moreover, if you can calculate something by SQL means, do that. But if it cannot be done by SQL, or if you do not have enough knowledge of SQL, Exportizer calculated fields can help.

Practical Steps

  1. Open your database.
  2. Open your source table or SQL query and click Export button .
  3. Choose the needed export format.
  4. Specify export options.
  5. Proceed to the Field Mappings step. Here, you specify the correspondence between the source and target fields / columns. The mappings for physical columns are built automatically. And you can add your calculated fields here:
    • Click Add button. A new row will be added to the grid.
    • Activate the left-most cell of the added row, and click '...' button there. An Expression Builder will appear. Here, you can define the formula for your calculated field. This step might be not so easy for newbies, but once you write your first few formulas, things will look easier. For example, add_date_time( dataset_field_val( 1, 'order_date' ), 5 ) formula will extract order date from the corresponding physical field and add 5 days to it. You might want to see the detailed explanation of all available functions.
    • After your formula is ready, click OK, and it will appear in the cell where you clicked '...' button before. Note that the formula is inside a vle_expr macro; it allows the application to distinguish between physical fields and custom expressions.
    • Define the target field and its attributes (if any).
    • If you need to place your new field in another position among other fields, use Move Up and Move Down buttons.
    • Repeat that sequence of actions to add another calculated field, if you need.
    Calculated Fields Definition
  6. [Optional] Click Save button to save the field mappings to file for future use. Or you might want to save all export definitions (including field mappings and other selected options) by clicking Tools button and choosing Save Export Definitions....
  7. Click Export and check the results.

Practical Cases