I have a map that uses an Excel schema as the target.
Here are the column headers:
Retailer Store ID Invoice Date UPC QTY Unit Cost Extended Cost Retail Unit Price Extended Retail Gross Margin % Book/Magazine
I need to add an extra record at the end that summarizes the highlighted columns above, so I created a clone of the target record and used the sum function for each of those columns:
Those are all working properly, but I am stuck on how to sum the Extended Retail column because I am multiplying 2 input fields to get that value for each row:
$Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Retail_Price * $Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Quantity_Lowest_Consumer_Units_Moved
You can use local variables within the Data Mapper to achieve your requirement. Please use the below steps:
1) On the root level of the target schema create a local variable like "varInitiateSum" and save local variable value as "set-context('Sum',0)".
2) Create the below Local variables on record level of the target schema:
2.1. Create First local variable like "varqty" and save local variable value as "$Input_Case_2182_input/Root/Record/Quantity_Lowest_Consumer_Units_Moved".