Step 2: Configure field mapping & data transformation

After configuring SFTP, you need to configure the customer or transaction block to map the headers of the CSV file with that of the Capillary system and automate data transformations.

According to the type of data of the selected template, you will see configuration options.

  1. In InTouch org, select the org ID for which you want to configure data mapping/transformation.
  2. In File Delimiter, enter the character used to separate each field in the CSV file - comma or tab. It is usually a comma (,).
  3. To convert the date format of date entries of the CSV file, check Format Transaction Date and select your preferred date format in Transaction Date Format.
704
  1. Configure each tab for the respective field mappings and data transformations.
  • extendedFields: Contains all transaction and line item level extended fields. Map each of the extended fields with the extended fields in the CSV file.
  • transaction: Contains all transaction fields. Map each of the transaction fields with the names in the CSV file.
  • attribution: Contains attribution headers.
  • lineitem: Contains all transaction line-item fields. Map each of the transaction line item fields with the names in the CSV file.
  • filter: Available for templates with Filters. For example, Transaction Add V2 with filter.
  • customFields: Contains all transaction and line item level custom fields. Map each of the custom fields with the custom field names of the CSV file.
  • Transformation: This is a field that allows you to automate the process or transforming data of the respective field. For example, you can have a net transaction amount which is the gross amount*quantity-discount. For any such automated computations for a field/entry, you can use the transformation column - To add or update transformation, click on the respective Edit icon.
    To know more about header mapping, data transformation expressions, and filter expressions with examples, navigate to the respective section.
  1. Click Continue to save and proceed to Step 3 to configure API authentication details.

Data Transformation using mapping & expressions

Attribution Headers
File headers mapping to API store or till attribution headers
Example 1: Attribute using Till Code

  • Header: Till_IUN

  • identifierType: const(TILL_CODE)

  • identifierValue: Till IUN
    Example 2: Attribute using Store Code

  • Header: Store Code

    • identifierType: const(STORE_CODE)
    • identifierValue: Store Code

Filter Expressions

Example: Process dataflow only if a transaction is paid or refunded.

  • Headers: Financial Status
  • Filter header mapping (header): Financial Status
    * Filter expression: ${header_value:equals('paid'):or(${header_value:equals('refunded')})}

Data Transformation

Header Mapping
Mapping the API fields billAmount and billNumber with headers

  1. Header: BILL AMOUNT; Value Mapped: BILL AMOUNT
  2. Header: TRANSACTION ID; Value Mapped: TRANSACTION ID
    Assign a constant value
OperatorExpression FormatExample
constconst{header name}1. Expression to set the source as INSTORE:
const{INSTORE}
2. Expression to set Transaction Type as Not Interested:
const{NOT_INTERESTED}

Arithmetic expressions

Supported OperatorsExpression FormatExample
+
-
*
/
exp{hdr{header 1} [operator] hdr{header 2}}
Ensure the open or close bracket '{' '}'
Note: Ensure that the braces are matched properly (opened and closing).
Example 1: Compute Net Amount

Headers: LineItem_Amount, LineItem_Discount
Expression: exp{hdr{LineItem_Amount} - hdr{LineItem_Discount}}
Example 2: Compute net amount based on quantity
Headers: LineItem_Amount, Quantity
Expression: exp{hdr{LineItem_Amount} * hdr{Quantity}}
Example 3: Currency Conversion: INR to DOLLAR

Headers: LineItem_Amount
Expression: exp{hdr{LineItem_Amount} / 74}

String operations

Supported OperatorsExpression FormatExample
startsWith
endsWith
concat
substring
exp{string expression1.concat(string expression2)}Example 1: Concat two or more headers for Description.
Headers: Item_Name, Item_Purpose
Expression: exp{'hdr{Item_Name}'.concat('hdr{Item_Purpose}')}
Example 2: Concat two or more headers with fixed info for Item Description.
Headers: Item_Name, Item_Purpose
Expression: exp{'hdr{Item_Name}'.concat(' is used for ').concat('hdr{Item_Purpose}')}
Example 3: Remove '+' from the mobile number if exists.
Headers: Mobile_No
Expression: exp{ ('hdr{Mobile_No}'.startsWith('+')) ? 'hdr{Mobile_No}'.substring(1) : 'hdr{Mobile_No}' }

Conditional Operations

OperatorExpression FormatExample
? :exp{(conditional expression) ? expression1 : expression2}Example 1: Check if the length of MOBILE number is 10, add country code '+91' otherwise return the existing value.
Headers: Mobile_No
Expression: exp{('hdr{Mobile_No}'.length() == 10) ? '+91'.concat('hdr{Mobile_No}') : 'hdr{Mobile_No}' }
Example 2: Check if an item is returned: if yes, set transaction type value 'RETURN' else 'REGULAR'
Headers: Is_Returned (Possible values 'true' / 'false')
Expression: exp{ ('yes'.equalsIgnoreCase('hdr{Is_Returned}')) ? 'RETURN' : 'REGULAR' }

Complex expressions

OperatorExpressionExample
Example 1: Compute Non-fraction Qualified quantity based on Product Category.
if Product Category is any of FUEL, JEWEL, METAL then qualified quantity would be the difference of LineItem_Amount and LineItem_Discount divided by LineItem_Rate else it would be LineItem_Qty
Headers: Product_Category, LineItem_Discount, LineItem_Amount, LineItem_Rate, LineItem_Qty
Expression: exp{ ({'FUEL','JEWEL','METAL'}.contains('hdr{Product_Category}') ) ? T(Math).floor( (hdr{LineItem_Amount} - hdr{LineItem_Discount}) / hdr{LineItem_Rate}) : T(Math).floor(hdr{LineItem_Qty}) }