Summarizing Excel Spreadsheet Columns

Situation:

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

 

Solution:

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