UDF to sum the line items

[Former Member]

Hi,

I am looking for UDF to add data from multiple line items and create single target item. The line items are in order. We need to keep adding the value until their is change in key.

Source

Field1 key-Field2 Filed3

name1 1001 100

name1 1001 50

name1 1001 50

name2 1002 50

name2 1002 20

name2 1002 50

Target

name1 1001 200

name2 1002 120

[Former Member · Accepted solution]

Hello Sarvesh,

Just want to explain my problem in detail.

My structure is

DT_Node

- Node 1..unb

-- Node1 0..unb

---field1 1..unb

---keyfield2 1..unb

In my scenario mapping is working at field level using Display queue. when I use Test tab and test it with values there is problem. I gave four Node1 with two matching keyfield2 value. It is creating two field1's and keyfield2's but it is still creating four Node1 in the output. I gave field1 and keyfield2 as 0..unb in output. It created four Node1's, two with field1 and keyfield2 values and two Node1's without field1 and keyfield2s.

problem 2 : In the target Node1, There is field4 with the constant all the time with no source field. I tried to do the same like field1 but getting error as uneven no. of items in queue.

Thanks for help...

[Former Member · Accepted solution]

Hi Aroo,

Apply below mapping on Node1 0..unb to resolve your problem..

keyfield2---->Sort--->SplitByValue(Value Changed)--->collapseContext--->Node1

Regards,

Sarvesh

[Former Member]

Thanks Sarvesh,

It is working now. It is a great help from you. I am closing this thread as answered.

[Former Member]

Thanks Sarvesh,

It is working when I use the display queue. I can see the vaues being added But when I go and test whole Message mapping I am getting error "value missing for field1". I have node1 --- node1 mapping. Do I need to add any logic in mapping at the node level.

[Former Member]

Sorry for below explaination... You have to apply a logic on node level as well.

node1 to node1 mapping is ok. You don't need to add any logic on node level.

Edited by: Sarvesh Singh on Jul 27, 2010 2:23 PM

[Former Member]

Hi, I am giving the exact source and target structure. This is just a prototype I am trying. Thanks for all for helping me on this.

source structure

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="Test" targetNamespace="Test">

<xsd:complexType name="DT_Test1">

<xsd:sequence>

<xsd:element name="Test" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="node1" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="field1" type="xsd:string" />

<xsd:element name="keyfield2" type="xsd:string" />

<xsd:element name="field3" type="xsd:decimal" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

Target Structure

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="Test" targetNamespace="Test">

<xsd:complexType name="DT_Test2">

<xsd:sequence>

<xsd:element name="Test2" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Node1" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="field1" type="xsd:string" />

<xsd:element name="keyfield2" type="xsd:string" />

<xsd:element name="field3" type="xsd:decimal" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

Edited by: Aroo Reddy on Jul 23, 2010 11:57 AM

[Former Member]

Thanks Sarvesh. this is still not working for me. All the nodes are repeated in target as well(even with matching value). I am mapping node1 to node1. Do I need to add any logic in node mapping as well.

I needed to add the field3 values for all matching keyfield2 value. As given, I used sum function in the mapping but I am getting same nodes as input without any additions.

Edited by: Aroo Reddy on Jul 23, 2010 10:43 AM

I used the Sum function but I am getting same nodes as source. No addition is happening

Hareenkumar

I need to add field3 contents based on keyfield2 values. In the mapping given, you are adding keyfield2 values. itself. can you give me updated mapping. Thanks

[Former Member]

Hi Reddy,

Can you give the source structure, source xml and target structure so that somebody can help you out here?

Regards,

---Satish

[Former Member]

The solution which I have give should work 100% because I have tested it by my own.

I think there is some context issue, Set the context of all source fields to the highest node. ( There shouldn't be any context change when you map the source fields ). Alternatively you can also use "RemoveContext".

For example...

keyfield2--->RemoveContext----|
                              |------>SortByKey---------------------->| 
field3--->RemoveContext------ |                                       |                                                                                
|-->FormatByExample--->Sum--->field3
keyfield2--->RemoveContext--->Sort--->SplitByValue(Value Changed)---->|

Try this and let us know.

[Former Member]

Thanks for the response. My stucture is

node1

- field1

- keyfield2

- field3

This node is repeaded based on the number of records. My output node is the same. How can I use the add function in my mapping. I am adding the values of the same field in different nodes until the keyfield2 value remains same. I need to to write only one node in the output for all mathing keyfield2 values.

Can you give me the mapping structure.

Thanks in advance...

[Former Member]

Do the mapping as shown below..

keyfield2------|
               |------>SortByKey--->SplitByValue(Value Changed)--->collapseContext--->SplitByValue(Each value)-->field1
field1---------|


keyfield2---->Sort--->SplitByValue(Value Changed)--->collapseContext--->SplitByValue(Each value)-->keyfield2



keyfield2-------|
                |------>SortByKey--->SplitByValue(Value Changed)--->Sum--->SplitByValue(Each value)-->field3
field3----------|

[Former Member]

Hi ,

As per your perception, I understand that you would like to add the same fields until the keyfield2 value remains same. And would like to have only one field in the output node that will have mathing keyfield2 value.

Please follow the mapping defined below:

keyfield2>Splitbyvalue(Value change)> UDF(use above provided UDF)>collapse context>splitby value(each value)--target field.

This will meet your requirement. Please get back if not resolved..

Thanks,

Hareen

Edited by: Hareenkumar on Jul 23, 2010 3:57 PM

[Former Member]

The mapping for field3 given in my previous reply will not work, so use this one instead...

keyfield2-------|
                |------>SortByKey------------------->| 
field3----------                                     |                                                                                
|-->FormatByExample--->Sum--->field3
keyfield2--->Sort--->SplitByValue(Value Changed)---->|

[Former Member]

Hi,

Try this UDF to add the items.

int i = 0 ;

int counter1 = 0;

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

{

counter1 = counter1 + 1;

result.addValue(a<i>);

}

result.addValue("");

Thanks,

Hareen

[rajasekhar_reddy14]

Hi,

You want to append Soure 3 fields to target and separator is space.you dont required UDF,use Concatanation function

Field1 ------->
                       Concat--------------->
                                                          Concat----->Target
  key-Field2 -->               Filed3---->

it is very simple.

Regards,

Raj

[former_member200962]

We need to keep adding the value until their is change in key.

For the above requirement (i think) we have to use the add function and the SplitByValue function.....concat wont provide the sum for a set of key values

Regards,

Abhishek.

[rajasekhar_reddy14]

Hi Abhishek,

Yes i think you are correct, i understood requirement wrongly...

thanks for correcting me.

Regards,

raj