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.
- In InTouch org, select the org ID for which you want to configure data mapping/transformation.
- In File Delimiter, enter the character used to separate each field in the CSV file - comma or tab. It is usually a comma (,).
- 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.
- 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.
- 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
- Header: BILL AMOUNT; Value Mapped: BILL AMOUNT
- Header: TRANSACTION ID; Value Mapped: TRANSACTION ID
Assign a constant value
| Operator | Expression Format | Example |
|---|---|---|
| const | const{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 Operators | Expression Format | Example |
|---|---|---|
| + - * / | 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 Operators | Expression Format | Example |
|---|---|---|
| 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
| Operator | Expression Format | Example |
|---|---|---|
| ? : | 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
| Operator | Expression | Example |
|---|---|---|
| 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}) } |
Updated about 1 year ago