The Formula Builder lets you create many different types of formulas in the Workbench.
Formula Builder General Information#
When opening the Formula Builder, 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 'Functions' allows you to browse all available functions both by category or a search bar
- the tab 'Columns' allows you to toggle the column visibility and therefore only present the required columns for the function you want to apply
Accessing the Formula Builder#
You have two options to access the Formula Builder:
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 Builder"
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#
Clicking on a column adds the column to the editor field.
To apply a formula:
Switch to the "Functions" tab. The functions configuration opens.
Click on "+" to create a new column. The new column appears in the preview.
If needed, rename the new column and confirm.
Select the required function: Type in the function name in the search bar or select a category from the left side and then select the needed function.
Click on "Add to Formula". The default formula appears in the Formula Builder. All adjustable formula parts are highlighted.
Replace the highlighted sections with the needed expressions 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 "Apply". The Formula Builder closes. The transformation is displayed in the operation stack.
Note that you can 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.