How are formulas used in 42Layers?

Supported Formulas

Common Use Cases

Getting Started

Color Coding

Editing or Deleting Training Examples

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.

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.

â€‹Date & Time Formulasâ€‹

â€‹Math Formulasâ€‹

â€‹Logical Formulasâ€‹

â€‹Text Formulasâ€‹

Commonly used formulas include CONCAT and DATE.

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.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.

**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.

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 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.

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.