Summarizing Excel Spreadsheet Columns


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:


sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Quantity_Lowest_Consumer_Units_Moved )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Net_Cost )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Extended_Cost_Amount )

sum($Input_AC_Item_Movement_VDE_records/Root/record-HIUVDE/record-INV/record-IMITM/Retail_Price )


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