Records sort and sum

1

Looking for a good solution here.

Looking below you can see there are two records with the same client id and same items. I want to sum it by client and item

<root>
<record>
	<client>123</client>
	<lines>
		<line>
			<item>01-01</item>
			<cost>10</cost>
		</line>
		<line>
			<item>02-02</item>
			<cost>20</cost>
		</line>
		<line>
			<item>01-03</item>
			<cost>30</cost>
		</line>
	</lines>
</record>
<record>
	<client>123</client>
	<lines>
		<line>
			<item>01-01</item>
			<cost>5</cost>
		</line>
		<line>
			<item>02-02</item>
			<cost>5</cost>
		</line>
		<line>
			<item>01-03</item>
			<cost>5</cost>
		</line>
	</lines>
</record>

Normally I would loop through each record and if the client ID is the same I would write the line then once it changes, close the record and start a new one. so i would end up like so

<root>
<record>
	<client>123</client>
	<lines>
		<line>
			<item>01-01</item>
			<cost>10</cost>
		</line>
		<line>
			<item>02-02</item>
			<cost>20</cost>
		</line>
		<line>
			<item>01-03</item>
			<cost>30</cost>
		</line>
		<line>
			<item>01-01</item>
			<cost>5</cost>
		</line>
		<line>
			<item>02-02</item>
			<cost>5</cost>
		</line>
		<line>
			<item>01-03</item>
			<cost>5</cost>
		</line>
	</lines>
</record>

Then I would sort lines by item then do the same. loop through each record, and then each line. item code is the same, sum it.

So what i would like to see output is…

<root>
<record>
	<client>123</client>
	<lines>
		<line>
			<item>01-01</item>
			<cost>15</cost>
		</line>
		<line>
			<item>02-02</item>
			<cost>25</cost>
		</line>
		<line>
			<item>01-03</item>
			<cost>35</cost>
		</line>
	</lines>
</record>

The catch I have found is the sort module will not sort the items. I am assuming it is because of the “-” in the item code. They dont change order at all.

So, thoughts on the most efficient way to handle this? I say efficient because there will potentially be 100k+ lines of data I am dealing with.

2

Hi Brent,

I can only recommend to do this logic in Java. I would loop through the outer XML client records and keep a hash map of client ids that hold another hashmap of items. Then have an inner loop for each client record that updates the item hashmap with the count of each item. Then the logic would iterate through the hashmaps in turn to generate the output XML.

Robert