Using Formulas


  1. How are formulas used in 42Layers?

  2. Supported Formulas

  3. Common Use Cases

  4. Getting Started

  5. Color Coding

  6. Editing or Deleting Training Examples

How are formulas used in 42Layers?

Formulas are used to quickly transform and map your source data to the columns in the output table. We have several predefined formulas (such as CONCAT, DATE, ADD, IF, etc.) that can be used together to complete complex transformations.

Supported Formulas

We have several formulas that fall into one of the following four categories noted below. For more information about our formulas, navigate to one of the pages below.

Common Use Cases

Commonly used formulas include CONCAT and DATE.

  1. The CONCAT formula outputs concatenated input values. This is useful for combining data from multiple source columns, or adding a constant value to every source record for a specific column.

  2. The DATE formula converts the input into a date. This is useful for extracting dates from a string, and/or converting the format of the date.

Getting Started

Step 1: Click on a cell in the output table to get started.

Step 2: Type = to see the list of available formulas.

Step 3: Continue typing the name of the formula or scroll through the list of formulas to find the one you are looking for, then select the formula.

For the example below, we are using the DATE formula.

Step 4: Provide formula input(s) according to the format show in the helper box for that specific formula. Ensure that the formula is complete and the closing parenthesis is included.

Note: Inputs within square brackets are optional.

For this example, we are using the Date source column as the input for the formula. We have left the second argument (input_format) blank (see the Date Formula for more info) and have specified the output format using our supported date-time formats.

Step 5: After providing the input(s) for the formula, press enter or click on the blue enter button to submit the formula. The formula will output values for each cell in that column.

Note: Formulas apply to an entire output column, not a single cell in a column; therefore, only one cell can contain formula(s) for a given column.

In the example below (click to expand) you can see that we have mapped the Date source column (on the left) to the Date destination column (on the right), and changed the date format based output format specified in the formula.

Step 6: Review the output column to confirm that the outputs for each row look accurate. If you need to make any changes to the formula, select the cell that contains the original formula, edit the formula, then press enter again.

Note: All of our formulas apply to the entire output column; therefore, only one cell per column can contain formulas.

Color Coding

Cells with Black Borders

Cells that contain formulas, column mapping or training examples have black borders. If you want to adjust the output for that column, click on these cells to delete or edit them.

Cells with Red Borders

Cells that have errors will have a red border. The cell that contains the formula can have an error, and other cells within that column can have errors. In either case, you must edit the formula to resolve any errors within a column containing a formula.

Editing and Deleting Formulas

If you want to edit a formula, click on the cell that contains the formula, edit the formula, then press enter to submit the revised formula.

If you want delete a specific formula, click on the cell that contains the formula, then click on the delete button to the right of that cell.