Summing a Field in a Select Data Set

Situation:

My input file has multiple PO’s with multiple Lines for each PO. Each line generates an output record like this:

 

Input                                                   Output

PO          LINE       QTY                       PO          LINE       QTY        POTotal

1             1             5                ->          1             1             5             10

1             2             5                ->          1             2             5             10

2             1             15             ->           2             1             15           25          

2             2             5               ->           2             2             5             25          

2             3             5               ->           2             3             5             25

 

My issue is I want to sum all the QTY fields for input nodes of a certain PO. It the above case the output field POTotal in output record 1 needs to sum the total of all QTY fields for input records that have the PO field equal to 1.

 

Solution:

This can be achieved by using the following steps:

1) Create a source schema for the input file

2) Create a target schema for the input file

3) Load the source and target schema within the data mapper

5) Since we'll be processing one record in to one record out, we'll need to implement the For Each function on the target record node as explained here: http://support.adeptia.com/entries/21262632-setting-for-each-rule-in-mapping

 

6) Next, create a local variable called "varCurrentPO" and select the PO field as the Local Variable Value. This variable will hold the value of the PO that we'll use to sum the QTY field.

 

7) After mapping the PO, LINE, and QTY fields, we'll need to map the PO Total field.

8) Select POTotal on the target side and the Textual Rules tab.

9) Open the Aggregation menu and select the Sum function

10) Within the parenthesis select the QTY field, which is the field we need to sum

11) After "Record" we'll define the condition by which the QTY field should be summed. In this case, when the PO field is the same value.

newmapper.JPG

 

12) Save this mapping and your POTotal field will equal the QTY fields for input nodes of a certain PO.

 

 

 

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.