"Group By" in data mapper

[_AnonymousUser]

Hi,
In data mapper, I have an input file like
id;zipcode
1;44100
1;44200
2;69002
2;56410
2;56100
I would like to be able to do a "group by" according to the id and get such an output files:
<Root>
<id>1</id>
<Zips>
<Zipcode>44100</Zipcode>
<Zipcode>44200</Zipcode>
</Zips>
<id>2</id>
<Zips>
<Zipcode>69002</Zipcode>
<Zipcode>56410</Zipcode>
<Zipcode>56100</Zipcode>
</Zips>
</Root>

I tried many options but don't manage to do that... In other editor mapping tools, it is possible, but with data mapper I cannot find any way to do it.
Any suggestion ?
NB:I want to do that in data mapper not in tXmlMap.
Regards.
Eric

[Anonymous]

Hi Eric,
Thanks for posting your issue here.
We have redirected your issue to TDM expert then come back you as soon as we can.
Best regards
Sabrina

[Anonymous]

Attached is a possible solution to this question:
TDM-GROUPBY-V6.1.1.zip.zip

Do a Test Run on the MAP_GROUP_BY map. This map is linked to the MAP_DISTINCT map (look at the MAP_GROUP_BY properties).

Hope this helps.

Fady on behalf of Haitao

[_AnonymousUser]

Hi,

Thank you very much, it works perfectly !

I didn't know we could enter a next map in properties of a map so as to be able to run another map after a first one (I might be wrong but I didn't find anything about that in the documentation)

And I didn't know this field "Distinct Child Element" in loop properties (It is in documentation but missed it...)

That would be a great exercise to add in TDM training.

Nontheless, when I enter the map to remove duplicate in the field "Next Map" and then run a test:
1-I get a popup with an error message (See file enclosed)
ERROR_MESSAGE.txt
2-Then I always have these warnings:

It looks like it doesn't keep from running the test but it is weird (Especially the first one. I thought there was Something wrong with the map). Didn't you have them ?
Regards.
Eric

[Anonymous]

I added the following JIRA to add documentation.
DOCT-6910

[Anonymous]

Hello,
I had a XML Document like below,
<?xml version="1.0" encoding="UTF-8"?>
<CustomerPurchaseOrder>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>John</CustomerName>
  <Amount>25000</Amount>
  <Address>Edina</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>John</CustomerName>
  <Amount>35000</Amount>
  <Address>Edina</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>456</CustomerEmployeeId>
  <CustomerName>Jimmy</CustomerName>
  <Amount>45000</Amount>
  <Address>EdenPrairie</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>John</CustomerName>
  <Amount>65000</Amount>
  <Address>Edina</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Randy</CustomerName>
  <Amount>45000</Amount>
  <Address>Austin</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>456</CustomerEmployeeId>
  <CustomerName>Jimmy</CustomerName>
  <Amount>55000</Amount>
  <Address>EdenPrairie</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Randy</CustomerName>
  <Amount>55000</Amount>
  <Address>Austin</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>John</CustomerName>
  <Amount>25000</Amount>
  <Address>Edina</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>007</CustomerEmployeeId>
  <CustomerName>Raghu</CustomerName>
  <Amount>35000</Amount>
  <Address>Houston</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>456</CustomerEmployeeId>
  <CustomerName>Jimmy</CustomerName>
  <Amount>45000</Amount>
  <Address>EdenPrairie</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>John</CustomerName>
  <Amount>35000</Amount>
  <Address>Edina</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Randy</CustomerName>
  <Amount>45000</Amount>
  <Address>Austin</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>007</CustomerEmployeeId>
  <CustomerName>Raghu</CustomerName>
  <Amount>65000</Amount>
  <Address>Houston</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Randy</CustomerName>
  <Amount>55000</Amount>
  <Address>Austin</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>Raghava</CustomerName>
  <Amount>35000</Amount>
  <Address>Wisconson</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Lara</CustomerName>
  <Amount>55000</Amount>
  <Address>Newyork</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>789</CustomerEmployeeId>
  <CustomerName>Randy</CustomerName>
  <Amount>55000</Amount>
  <Address>Austin</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>123</CustomerEmployeeId>
  <CustomerName>Raghava</CustomerName>
  <Amount>35000</Amount>
  <Address>Wisconson</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>999</CustomerEmployeeId>
  <CustomerName>Kohli</CustomerName>
  <Amount>55000</Amount>
  <Address>Punjab</Address>
 </CustomerInfo>
 <CustomerInfo>
  <CustomerEmployeeId>999</CustomerEmployeeId>
  <CustomerName>Virat</CustomerName>
  <Amount>95000</Amount>
  <Address>Pune</Address>
 </CustomerInfo>
</CustomerPurchaseOrder>

And I want to Print All the Amounts In One Record Based on the "CustomerEmployeeId" and "Name" in TDM.
The Output Looks like This
CustomerEmployeeId;CustomerName;Amount;Address
123;John;Total Amounts Of Id123 Based Upon The Id;address
Please help me out  and which Data mapper fuction can I use for Sum the Total Amounts, and I tried All the Aggregate Functions In The Talend Data Mapper But I didn't Get the Proper Response.

[Anonymous]

challagundla,
I created a job with Distinct Child Element and a Agsum on the amount field

I could not attach the job. I will send it in a separate email.
I could not get this to work so I'm going to ping R&D to see what I'm doing wrong.
My data is very similar to yours and I'm using a de-normalized format assuming the agsum is in the second map.
The attached map is only the distinct second map.

[_AnonymousUser]

Please find enclosed a solution.                                               
AGGREGATE_… .zip
There are two maps:
1-LOOP_AMOUNT
-Sort per customer ID
-Enable to get all amounts per customer (In a subloop)
2-AGGREGATE_AMOUNT
-Remove duplicate
-Aggregate amount

Let me know if it meets your requirements.
Regards.
Eric

[lakman1]

I'm struggeling by implementing a group by functionality as well. Unfortunately the example files above are not available any longer. Has anyone a hint for me, how I can group some subnodes by property and then to calc the sum?

The file has the structure below. I would like to group by the 'positionNumber' and then sum up the quantities, in this case 55 + 100 (first and third sub-node)

<ns:positions>

        <ns:position positionNumber="1">

            <ns:quantityFirstChoice>55</ns:quantityFirstChoice>

        </ns:position>

        <ns:position positionNumber="2">

            <ns:quantityFirstChoice>55</ns:quantityFirstChoice>

        </ns:position>

        <ns:position positionNumber="1">

            <ns:quantityFirstChoice>100</ns:quantityFirstChoice>

        </ns:position>

</ns:positions>