Formula Wizard draft 0
ETL formulas are defined for imported tables. (Imported tables are the tables shown in the Browse Table window.)
Once formula fields have been defined they are listed as one of the Available Fields in the Import Table Wizard ... Select Fields window.
You may access the Formula Wizard using its gear icon on the top line of icons (it's the 3rd icon from the left), of via menu Tools > Wizard > Formula.
Formula Wizard Start page
The Formula Wizard Start page lists the available imported tables in the ETL on the left pane.
Select a table on the left pane and you'll see the available table's fields on the right panel. Gray-font fields are fields that were imported from the source table. Black-font fields, if available, are formulas created by the Formula Wizard.
Use the Display all Fields" check box to show only formulas or all fields available.
Once you selected a table on the left pane, you may do the following procedures from this wizard's page:
- Add a formula: click Add and go to Formula Wizard - Data Type page.
- Modify a formula: select a formula field (a black-font field), click Modify and go to Formula Wizard Expression page.
- Delete a formula: select a formula field (a black-font field), click Delete and confirm.
Formula Wizard Data Type page
This page appears after you click Add a formula on the Formula Wizard Start page. This will start the process to create a new formula field on the selected table.
From this page, you may:
- Click Back to cancel and return to the Formula Wizard Start page.
- Select a Data Type on the top right panel. If required, use the boxes below the Data Type panel to select the desired size and decimal parameters of the new formula field to be created.
- Click Add again to go to the Formula Wizard Expression page. You are still in the process to create the new formula field - nothing has changed until you click Save on the next page.
Formula Wizard Expression page
This page has several components as following:
- The top white panel gives you access to data that may be used in the expression (bottom white panel). On this panel, you'll always have access to the fields from the table that you are creating the new formula field (in the picture above, it's AR_InvoiceHistoryHeader table), as well as from Constant and System Info. If relationships have been created on this table in the ETL, then you may have access to its Parent and/or Children tables. Parent tables allow you to create new formulas to denormalize fields (ex.: bring CustomerName from parent Customer table to InvoiceDetails table), while Children tables allow you to create new formulas to summarize information (ex.: summarize sales from child InvoiceTable into CustomerTable).
- The top left buttons allow you to add fields from the top white panel into the bottom white panel where the actual expression will be created. Select a field on the top white panel and click Append to add to the bottom of the expression. If you want to insert or replace lines on the bottom white panel, select the field on the top panel, then the line on the bottom panel, and then click Insert or Replace.
The bottom white panel is where the actual expression is going to be created.
IF & CASE Statements in the Formula Wizard
Adding Making calculations after an If or Case Statement
You need to add both numeric fields first. Then select (highlight) the first numeric field, and click on the operator (+, -, X, /). The operator will then insert itself after the selected field.
You can put If statements inside a Case statement, but not a Case inside an If.
See also: ETL Workflows.