topic Re: "Group By" in data mapper in Talend Studio

"Group By" in data mapper

_AnonymousUser — Fri, 24 Jun 2016 11:13:44 GMT

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

Re: "Group By" in data mapper

Anonymous — Mon, 04 Jul 2016 07:25:26 GMT

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

Re: "Group By" in data mapper

Anonymous — Tue, 05 Jul 2016 13:17:15 GMT

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

Re: "Group By" in data mapper

_AnonymousUser — Thu, 07 Jul 2016 09:25:53 GMT

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

Re: "Group By" in data mapper

Anonymous — Fri, 22 Jul 2016 13:30:19 GMT

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

Re: "Group By" in data mapper

Anonymous — Fri, 22 Jul 2016 15:03:54 GMT

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.

Re: "Group By" in data mapper

Anonymous — Fri, 22 Jul 2016 20:09:46 GMT

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.

Re: "Group By" in data mapper

_AnonymousUser — Sat, 23 Jul 2016 08:22:19 GMT

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

Re: "Group By" in data mapper

lakman1 — Wed, 06 Jan 2021 15:11:06 GMT

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>