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".
2.2. Create Second local variable like "varretailprice" and save local variable value as "$Input_Case_2182_input/Root/Record/Retail_Price".
2.3. Create Third local variable like "varMul" and save local variable value as
"$varqty * $varretailprice"
2.4. Create Fourth local variable like "varGetSum" and save local variable value as
"get-context('Sum' , 0)".
2.5. Create Fifth local variable like "varSum" and save local variable value as
"set-context('Sum' , $varMul + $varGetSum )".
3) On the column "ExtendedRetail" of the Target Schema apply the below textual rule: "$Input_Case_2182_input/Root/Record/Quantity_Lowest_Consumer_Units_Moved * $Input_Case_2182_input/Root/Record/Retail_Price".
4) At last on the column "Extended Retail" in the clone of the Target Record Schema apply the textual rule: "get-context('Sum' , 0)" .