The Formula Wizard has two main functions:
- Defines and names new columns known as formulas or formula fields. Each formula is created in one imported tables.
- Define the calculation (formula) that determines the value of the data loaded into the formula field.
In informal usage the term "formula" refers to both the calculation defined in the Formula Wizard and the column/field in the imported table the formula is assigned to. Other authorities and ETL systems refer to formulas as computed fields, computed columns, computed values, and calculated fields/columns/values.
Formulas may include numeric calculations, and/or mathematical, statistical, or character functions. Forumulas in the ETL are similar to the formulas that you might add to a cell in an Excel spreadsheet.
- 1 Formula Wizard Start window (ALT-M)
- 2 Formula Wizard Data Type window
- 3 Formula Wizard Expression page
- 4 Discussion
Formula Wizard Start window (ALT-M)
Lists the imported tables in the current project. Select a imported table in the Tables box and you will see the table's formulas and other imported fields Formulas (and Fields) box.
Formulas (and Fields) box
Displays the formulas and other imported fields from the table selected in the Tables box.
- Formula fields are displayed in black font. These formulas are formulas previously defined for the selected table by this wizard.
- (Non-formula) Fields are displayed in gray font. These fields are columns previously specified for the selected table in the Import Table Wizard. The display of these fields can be toggled off or on by Display all Fields.
Display all Fields checkbox
Use the check box to control the display of (non-formula) Fields in the Formula (and Fields) box. Only formulas or all fields and formulas can be displayed.
Click Add to add a new formula to the selected table. This will take you to the Formula Wizard - Data Type window.
Click Modify to change the formula selected in Formula(and Fields). This will take you to the 'Formula Wizard Expression' window.
Click Delete to delete the formula selected in Formula(and Fields).
Formula Wizard Data Type window
This page appears after you click Add on the Formula Wizard Start window. As the title implies this window specifies the data type information for the new formula.
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.
Building a Formula Expression in the Bottom White Panel
- Use Append/Insert/Replace to add all required fields from the top white panel to the bottom white panel.
- To insert, you have to first select the line in the bottom white panel where to insert, select the field on the top white panel, and then click Insert.
- To replace, you have to first select the line in the bottom white panel to be replaced, select the field on the top white panel, and then click Replace.
- To add an operator '+' or '-' or 'x' or '/' between two fields, select the first field and click the operator button.
- To apply a function to a field, select the field and click 'f(x)', and follow the wizard.
- To add a 'Case' statement, select the first field to be included in the statement, click 'Case', select an existing filter, click 'Insert'.
- To add an 'If' statement, select the first field to be included in the statement, click 'If', select an existing filter, click 'Insert'. Now this first field will become the expression executed if the filter criteria is met. Now click the first field again and select either 'End If' to finish the statement, or 'Else' to build the 'else' part of the statement. If else is included, please add an 'End If' at the end of the expression.
- Click 'Save' to save the formula.
Starting the Formula Wizard (ALT-M)
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.
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.