Message Mapping - SUM a field per unique combination of 2 other fields?

[susan_pfab]

I'm receiving a proxy with detail records, and I need to create a subtotals file. Here an example:

BEFORE MAPPING

<ROW>

<KOSTL>0000010300</KOSTL>

<POSID>DUMMY</POSID>

<FTE>0.51</FTE>

</ROW>

<ROW>

<KOSTL>0000010400</KOSTL>

<POSID>123</POSID>

<FTE>0.49</FTE>

</ROW>

<ROW>

<KOSTL>0000010400</KOSTL>

<POSID>DUMMY</POSID>

<FTE>0.72</FTE>

</ROW>

<ROW>

<KOSTL>0000010400</KOSTL>

<POSID>123</POSID>

<FTE>0.82</FTE>

</ROW>

<ROW>

<KOSTL>0000010300</KOSTL>

<POSID>DUMMY</POSID>

<FTE>0.18</FTE>

</ROW>

AFTER MAPPING:

<ROW>

<KOSTL>0000010300</KOSTL>

<POSID>DUMMY</POSID>

<FTE>0.59</FTE>

</ROW>

<ROW>

<KOSTL>0000010400</KOSTL>

<POSID>123</POSID>

<FTE>1.31</FTE>

</ROW>

<ROW>

<KOSTL>0000010400</KOSTL>

<POSID>DUMMY</POSID>

<FTE>0.72</FTE>

</ROW>

How do I create a new record for all unquie combinations of KOSTL and POSID? And total the FTE field for each line? Can this be done in a message mapping?

[markangelo_dihiansan · Accepted solution]

Hello,

How do I create a new record for all unquie combinations of KOSTL and POSID? And total the FTE field for each line? Can this be done in a message mapping?

Yes, very much possible with or without UDF. See sample mapping below without using UDF:

For ROW:

KOSTL(set context to row) -> concat: -> sort: case insensitive ascending -> splitByValue:valueChanged -> collapseContext -> ROW
POSID(set context to row) ->  /

For KOSTL:

KOSTL(set context to row) -> concat: ----------------> sortByKey: case insensitive ascending -> formatByExample -> collapseContext -> splitByValue:eachValue -> KOSTL
POSID(set context to row) -> /      KOSTL(set context to row) ->/                                          /
                                                                                /
KOSTL(set context to row) -> concat: -> sort: case insensitive ascending -> splitByValue:valueChanged -> / 
POSID(set context to row) ->  /

For POSID:

KOSTL(set context to row) -> concat: ----------------> sortByKey: case insensitive ascending -> formatByExample -> collapseContext -> splitByValue:eachValue -> POSID
POSID(set context to row) -> /      POSID(set context to row) ->/                                          /
                                                                                /
KOSTL(set context to row) -> concat: -> sort: case insensitive ascending -> splitByValue:valueChanged -> / 
POSID(set context to row) ->  /

For FTE:

KOSTL(set context to row) -> concat: ----------------> sortByKey: case insensitive ascending -> formatByExample -> sum -> splitByValue:eachValue -> FTE
POSID(set context to row) -> /      FTE(set context to row) ->/                                            /
                                                                                /
KOSTL(set context to row) -> concat: -> sort: case insensitive ascending -> splitByValue:valueChanged -> / 
POSID(set context to row) ->  /

Hope this helps,

Mark

[susan_pfab · Accepted solution]

Hi Mark,

I need your help again. Now, they want to add a filter based on a new field being added to the sender proxy structure. How do I incorporate this filter into the ROW mapping? I have tried just adding an if statment, but the totals don't work in this case.

[markangelo_dihiansan · Accepted solution]

Hello,

Apologies, I was not watching this thread.

Now, they want to add a filter based on a new field being added to the sender proxy structure. How do I incorporate this filter into the ROW mapping?

Can you paste your sample and its expected output after mapping?

Regards,

Mark