Challanging XI mapping question - summing an element based on another eleme

[Former Member]

My outbound message is a PSV file as shown.

H|A|01|10|000000000000040423|00000002|0010|00|0000000000|

H|A|01|10|000000000000041462|00000002|0020|00|0000000000|

H|A|01|10|000000000000041102|00000004|0000|00|0000000000|

H|A|01|10|000000000000041203|00000004|0001|00|0000000000|

H|A|01|10|000000000000041731|00000004|0003|00|0000000000|

H|A|01|10|000000000000041346|00000007|0046|00|0000000000|

H|A|01|10|000000000000041475|00000007|0009|00|0000000000|

H|A|01|10|000000000000010306|00000008|0003|00|0000000000|

H|A|01|10|000000000000010307|00000008|0019|00|0000000000|

H|A|01|10|000000000000040022|00000008|0056|00|0000000000|

H|A|01|10|000000000000040370|00000008|0030|00|0000000000|

H|A|01|10|000000000000040406|00000008|0040|00|0000000000|

H|A|01|10|000000000000040856|00000008|0000|00|0000000000|

Please take a look at the 6th element in the above message. You will see multiple records with same value for this element. My output structure for this input looks like this:

H|A|01|10|000000000000041462|00000002|0030|00|0000000000|

H|A|01|10|000000000000041731|00000004|0004|00|0000000000|

H|A|01|10|000000000000041475|00000007|0055|00|0000000000|

H|A|01|10|000000000000040856|00000008|0148|00|0000000000|

Rules:

1. There is one record at the output for all the input records that have the same value for element6.

2. The element7 in the output record is sum of all the element7s in the input that have the same value for element6.

3. Other elements in the output record get the values from the last record with the same value for element6.

I can do the file content conversion and no problem there.

How do I perform the mapping as expplained above?

Really apprecite your help. I wish I could award more points than that is allowed for the right answer!

Thank you.

[Former Member]

Hi Param,

Eventhough scenario is complex, I think solution is simple.

You need to write an UDF which will take input as your 6th and 7th elements. Pass all row data to UDF be setting context level at highest level. Now base on change in value of 6th element, add that value to the output resultset.

Similar for 7th element pass all row data and you can sum the values inside UDF and set to output your sum value for every change in 6th element..n so on.

I this can be achievable using UDF.

Let us know if you need more details.

Nilesh

[Former Member]

Nilesh,

Thank you for the reply. Could you post some java code for this?

[Former Member]

Hi Param,

For generating 6th element you can write code something like this...

String v_lastval = "";

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

if( !a<i>.equals(v_lastval)) /// if not equal to last value add in output.

result.addValue(a<i>);

v_lastval = a<i>;

}

where a -- > 6th element

For 7th element

String v_lastval = "";

double sum =0;

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

sum = sum + Double.parseDouble(b<i>);

if( !a<i>.equals(v_lastval))

result.addValue(Integer.parseInt(sum));

sum =0;

}

where a -- > 6th element

and b -- > 7th element

This is just sample code..you need to test and do changes accordingly.

Nilesh