Add transforms - Automate_Studio - 20.3

Automate Studio with Connect User Guide

Product type
Software
Portfolio
Integrate
Product family
Automate
Product
Automate > Automate Studio
Version
20.3
Language
English
Product name
Automate Studio
Title
Automate Studio with Connect User Guide
First publish date
2018
Last updated
2024-10-22
Published on
2024-10-22T07:54:52.852256

By using Excel formulas during the mapping stage, you can transform data before uploading or downloading it. This feature is available for Excel data files.

  1. In the Mapper, click the field that contains the values you want to transform. On the ribbon, click Add Transform.
  2. In the Transform Rule box, enter the Excel formula.

    In the example below, the customer has added formula ({=(H2-2.5) for the field Net Weight which is mapped to column H. This formula will be applied to net weight values that start from cell H2, and the transformed value will be stored in column J from cell J2 onwards. If "Transform Original Mapping" is checked then the transform value will be copied from J column to H column of Net Weight.

    The spreadsheet includes the formula in the header row.

    header with field name description and formula

    Note:
    • If the formula is offset by one or more rows, you can specify that in the Row where formula will be evaluated box. For example, if you offset the formula above by one row, type 3 in the box.

      If you are using a date in the formula, wrap the formula in the =TEXT( , "mm/dd/yyyy") function. For example, if the formula is =today()+1, enter =TEXT(today()+1, "mm/dd/yyyy").

      Transforms on header fields apply only to rows that contain header data, and transforms on line-item fields apply only to rows that contain line-item data. If you have header and line-item data mapped to the same column, you will need to add the transform twice.

    • Transform formulas may be entered in all languages supported by Microsoft Excel.
  3. To validate the Excel formula, click Validate.
  4. Do one of the following:
    1. To overwrite the original data, change column letter in the Column box to the original column—in the example, F. Click OK.
    2. To retain the original data, click OK.