Mapping - sum of line items

[Former Member]

Hi Experts,

I am cutrrently working on File (CSV) --> Proxy , here iam looking for mapping logic to add data from multiple line items and create single target item. The line items are not in order. We need to keep adding the value based on validation rule.

Rule: IF Store No, Date, and TRNo are same then add the amount and map it to target amount (Sales amount)  else pass the amount to
Sales amount.

Source File (CSV File) --> Proxy

8001,20120305, 79046, 98.00

8001, 20120305, 79048,170.10

8023,20120305, 130572, 81.67

8023,20120305, 130572, 145.83

Source Structure:

MT_Details

          Store No

           Date

          TRNo

          Amount

Target Structure:

       MT_Target

             Store_ID

             Date

             Tr.No

            SalesAmount  

Thanks in advance.

Best Regards,

Chandu     

[iaki_vila]

Hi Chandu,

I think that the biggest problem is that the source XML is not sorted.

You could do XSL + Graphical Mapping.

At first your sort the XML source, for example you could follow this example that makes two consecutives sorts http://www.adp-gmbh.ch/xml/xsl/example_10.html.

When you have your XML source sorted you only need to group in contexts an make the amount sum

Regards.

[baskar_gopalakrishnan2]

Few cents...

Doing java mapping is much simpler in this case.  You need to check three individual fields of every line item record and decide the count logic for the last field amount.  Your requirement is not fully clear.

Question: Since you mentioned target item is single record, how do you decide for the first three fields values for mapping? I meant what value you will assign last record values or first record?

[Former Member]

Hi Baskar,

Thanks for the prompt reply... as you asked about the  mapping question , it will be one to one mapping  and values will be just pass through for first three records . but the only logic i am looking here is  for the sales amount where i have to check for the Sum of line items if the StoreNo,Date,Tr.No are same them add the amount and send it to the target.

And the java solution you sugessted , actually i am not the java geek , so it will of great help if you  have and UDF in place which you can share .

Thanks In advance,

Best Regards,

Chandu

[baskar_gopalakrishnan2]

>but the only logic i am looking here is  for the sales amount where i have to check for the Sum of line items if the StoreNo,Date,Tr.No are same them add the amount and send it to the target

That means only for the identical line item rows you count and other records you ignore and dont count. Is that right? Please elaborate

Sample UDF. This method looks for the similar records and sum the amount only for those records.

Create method using execution type:   All Values of  Queue;  Create four Arguments  with name var1, var2, var3 and var4 and of type Java String including amount. If you declared amount as int then you might have to change. This is just sample code tested in eclipse and it works. You might have to customize it according to your requirement. Hope that helps.

public void sumLineItems(String[] var1, String[] var2, String[] var3, String[] var4, ResultList result, Container container) throws StreamTransformationException{

  int similarLineItem = 0;

     for (int i =0, j=0, k=0, m=0; i < var1.length &&  j <  var2.length  &&  k <  var3.length  &&  m < var4.length;  i++, j++, k++, m++ ) {

        if ( var1[i].equals(var2[j]) && var2[j].equals(var3[k])) {

          similarLineItem = similarLineItem + new Integer(var4[m]).intValue();

       }

     }

     result.addValue(new String().valueOf(similarLineItem));

}

Mapping

all the four fields as input to the udf (sumLineItems) ---> map it to the target field Amount

[iaki_vila]

May be i'm little confused as well. If i understand well you could have the follow structure:

MT_Details

          Store No  X

          Date        B

          TRNo       C

          Amount    1

          Store No  Y

          Date        B

          TRNo       C

          Amount    3

          Store No  X

          Date        B

          TRNo       C

          Amount    5

And the output must be:

  MT_Target

       Store_ID   X

       Date         B

       Tr.No        C

       SalesAmount  6  

      Store No   Y

       Date        B

       TRNo       C

       Amount    3

Your count the line item Y altough they haven't any other record with the 3 first rows equals, isn't?

Regards

[Former Member]

Hi Bhaskar,

"but the only logic i am looking here is  for the sales amount where i have to check for the Sum of line items if the StoreNo,Date,Tr.No are same them add the amount and send it to the target

That means only for the identical line item rows you count and other records you ignore and dont count. Is that right? Please elaborate"

Yes you are correct , i have to chcek for the sume of line items if the StoreNo,Date,Tr.No are same then addthe amount and send it to the target Amount.

Thanks for the UDF , i will try and get back back to you on that.

Thanks & Best REgards,

Chandu

[Former Member]

Hi Vila,

Yes  your assumption of source and target  expected result is what exactly i amlooking for.

Thanks & Best REgards,

Chandu