Using the Formula Editor
The Formula Editor lets you create many different types of formulas in the Workbench.
Formula Editor General Information#
When opening the Formula Editor, the editor field is on top of the preview of the source columns. On the right we have two tabs wherein the functions can be configured:
- the tab 'Columns' allows you to toggle the column visibility and therefore only present the required columns for the function you want to apply
- the tab 'Functions' allows you to browse all available functions both by category or a search bar
Accessing the Formula Editor#
You have two options to access the Formula Editor:
- when you want to perform your first transformation on a Snowflake source, simply click on a "+" button next to a node in the Flow Area in the Workbench and then click on "Formula"
- when you've already performed an operation on a Snowflake source or a created Datameer view, click on "Add to Recipe" from the transformation recipe
Applying a Formula#
When opening the Formula Editor, the formula configuration field displays the name of the first column of your Snowflake source or Datameer view, containing the prefix 'Source'. You can delete this to start with a clean formula.
Note that, if needed, you can select all columns you want to apply your operation on first. For that select all required columns or deselect the columns you won't need and confirm with "Done". Per default, all columns are preselected.
To apply a formula:
Switch to the "Functions" tab on the right side. The functions configuration opens.
(Optional) Create a new column, if you want to serve the formula result in a complete new column.
Select the required function. Type in the function name in the search bar, select a category from the left side and then select the needed function or browse the 'All' category to see all applicable functions. When selected, click on "Add to Formula". The default formula appears in the Formula Builder. All adjustable formula parts are highlighted.
Replace the highlighted sections of the formula and confirm with "Apply Formula". An auto-complete feature gives you the ability to visualize and complete formulas efficiently. The formula proceeds and the result is displayed in the new column or, if applied to an existing column in there.
(Optional) Create and apply more formulas to your data set.
Confirm with "Done". The Formula Builder closes. The transformation is displayed in the operation stack.
Deleting a Formula#
To delete an already applied formula from the operation stack, just click on the "Delete" icon next to the formula name in the operation stack. The operation is deleted immediately.
Note that you use only Columns with supported data types, such as string, integer and date.
Note that the arguments within an applicable formula must match the destination data set value type. That means, e.g. for a formula with string values to be inserted, only columns that contain string values are applicable.
A formula can only reference columns from it's own data set. Referencing columns from different Projects is possible after a 'Join' transformation has first been applied and the columns of one data set have been joined to the data set you want to apply the function to.