Message Mapping - Sum of all Element1.Field1 where Element1.Field2 = X

[Former Member]

Hi!

Has everyone an idee of how to do the following message mapping ?

My input message is :

E1EDP01
   E1EDP26
      QUALF = 001
      BETRG = 5
   E1EDP26
      QUALF = 003
      BETRG = 3
   E1EDP26
      QUALF = 002
      BETRG = 5
   E1EDP26
      QUALF = 003
      BETRG = 3

I want to get the sum of all E1EDP26.BETRG where E1EDP26.QUALF = "003". There can be more than one E1EDP26 with field QUALF = "003". So expected result is 6.

Thanks in advance,

Laurence

[Former Member · Accepted solution]

Hi Laurence,

try this UDF...it has 2 inputs :

1. QUALF->removeContext->UDF--->invoiceAmount

2. BETRG->removeContext->UDF

UDF:

double cnt = 0;

int flag = 003;

for(int i = 0 ; i<a.length ; i++)

{

if(a<b>[</b>i<b>]</b>.equals("003") )

{

cnt = cnt + Double.parseDouble(b<b>[</b>i<b>]</b>) ;

}

}

result.addValue(""+cnt);

Thanks,

Maheshwari

[Former Member · Accepted solution]

Perfect, it works

Thanks a lot!

Laurence

[Former Member]

Hi Laurence,

can u please tell which field shd b mapped to which field?

as u said E1EDP26 can b more that 1 but in ur target structrure every node is having occurance 1 (?????)

if possible give input and output file..

Thanks,

Maheshwari.

[Former Member]

Input structure :

INVOIC02 1.1
     IDOC 1.1
          E1EDP01 0..999999
               E1EDP26 0..20
                    QUALF 0..1
                    BETRG 0.11

Output structure :

root 1..1
     ...
     GoodsDeclaration 1..1
          Invoice 0..1
               ExchangeRate 0..1
                    exchangeRate 0..1
                    currrency 1..1
                    exchangeDate 0..1
               invoiceAmount 0..1
          ...
     ...

The target field root/GoodsDeclaration/Invoice/invoiceAmount must be mapped with the sum of fields BETRG belonging to nodes E1EDP26 with field QUALF = "003".

Sample :

<INVOIC02>
     <IDOC>
          <E1EDP01>
               <E1EDP26>
                    <QUALF>001</QUALF>
                    <BETRG>5</BETRG>
               </E1EDP26>
               <E1EDP26>
                    <QUALF>003</QUALF>
                    <BETRG>1</BETRG>
               </E1EDP26>
               <E1EDP26>
                    <QUALF>002</QUALF>
                    <BETRG>5</BETRG>
               </E1EDP26>
               <E1EDP26>
                    <QUALF>003</QUALF>
                    <BETRG>2</BETRG>
               </E1EDP26>
          </E1EDP01>
     </IDOC>
</INVOIC02>

In this case, target field root/GoodsDeclaration/Invoice/invoiceAmount must have the value 3 :

<root>
     ...
     <GoodsDeclaration>
          <Invoice>
               <ExchangeRate>
                    <exchangeRate/>
                    <currrency/>
                    <exchangeDate/>
               </ExchangeRate>
               <invoiceAmount>3<invoiceAmount>
          </Invoice>
          ...
     </GoodsDeclaration>
     ...
</root>

Laurence

[Former Member]

Hi Laurence,

can you tell target structure with occurances?

Thanks,

Maheshwari.

[Former Member]

Hi Maheshwari,

This is the target structure :

root 1..1
     GoodsDeclaration 1..1
          Invoice 0..1
               ExchangeRate 0..1
                    exchangeRate 0..1
                    currrency 1..1
                    exchangeDate 0..1
               invoiceAmount 0..1

Thks,

Laurence

[Former Member]

Laurence,

This can be achieved in graphical mapping, if the target strcuture has 2 level hierarchy. Some thing like this

Result
    Sum

The expected result can be mapped to Sum.

For this map,

QUALF(change the context to E1EDP26)                                                                                
equalsS -- ifWithOutElse (condition)
                                                              /
Constant '003'


E1EDP26 --> remove context --> ifWithOutElse (then)

ifWithOutElse (output) --> Result

and

BETRG --> Remove context --> StandardFunction Sum --> Sum(target node)

Regards,

Jai Shankar

[Former Member]

Thanks for your response

First, when I test with your type of output structure (

Result
   Sum

I have no Result target field. If I set the context of QUALF to E1EDP01 instead of E1EDP26, I have the Result target field but the field Sum contains 13 instead of 3 (it takes all the fields BETRG undepending on the QUALF value).

Secondly, my output structure is no so simple

...
Invoice
   ExchangeRate
      exchangeRate
      currrency
      exchangeDatae
   invoiceAmount

My target field is the field invoiceAmount.

Kind regards,

Laurence

[Former Member]

Laurence,

2 options. One is use 2 different mapping with in the interface mapping and bring in the extra <b>result node</b> in the intermediate structure. So, now the mapping will be source to intermediate structure and next mapping from intermediate to target strucutre.

Option 2: Use Java mapping to handle this.

>>I have the Result target field but the field Sum contains 13 instead of 3

Yes , unless the field validation is done at the root level you will have all the values counted.

Regards,

Jai Shankar