Extract and aggregate data from 2 tables ( 1 to many relationship )

[_AnonymousUser]

I am relatively new to Talend and is trying to is to set up a job in TOS to solve the the following scenario:
Extract data from 2 sybase tables and sum(salary) value. Table 1 to Table 2 follows 1 to many relationship.
Table1
id, name
1 abc
2 pqr
Table2
id, salary
1 15
1 10
2 20
2 10
expected result:
1, abc, 25 ( sum (15, 10))
2, pqr 30 ( sum (20, 10 ))
How can this be modeled ? I couldn't get the tMap component to do the aggregation (sum (salary) part ). Please advise.

[Anonymous]

Hello
Using the sum function on tAggregateRow component, here is an example:
tFileInputDelimited_1:
1;15
1;10
2;20
2;10
tFileInputDelimited_2:
1;abc
2;pqr
Result:

Starting job forum5975 at 10:15 26/03/2009.
.--+----+----------.
|    tLogRow_1     |
|=-+----+---------=|
|id|name|sum_salary|
|=-+----+---------=|
|1 |abc |25        |
|2 |pqr |30        |
'--+----+----------'
Job forum5975 ended at 10:15 26/03/2009. 

Best regards

shong

[_AnonymousUser]

Thanks Shong, it works great

[Anonymous]

I have a need to use Talend to extract data from multiple tables from a sybase database and to generate an XML file for each record processed.
Award table
-------------
awd_id awd_title
1 Title1
2 Title2
Inst table
-----------
awd_id inst_id
1 111
1 121
2 222
As seen from the XML below, an Award can have 0, 1 or many institutions. What is the best way to model this scenario using Talend?
I was thinking of using the following components:
tSybaseInput ......tMap.....tFileOutputXML
Will this work, how do we iterate through each institution of an award. If i use all matches in the tMap, will i get all institutions for an award. If so how would be the mapping on the right side of tMap ?

<?xml version="1.0" encoding="UTF-8"?>
<Award xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Award.xsd">
<AwardID>AwardID</AwardID>
<AwardTitle>AwardTitle</AwardTitle>
-----
<Institutions>
<Institution>
<Name>Name</Name>
<StateCode>StateCode</StateCode>
<ZipCode>ZipCode</ZipCode>
</Institution>
</Institutions>
------
</Award>

[Anonymous]

Hello
You can use tAdvancedFileOutputXML instead of tFileOutputXML. On tAdvancedFileOutputXML component, you need set AwardID as group element and set Institution as loop element. So, you can generate a xml file like this:
<?xml version="1.0" encoding="UTF-8"?>
<Award id=1>
<Institution>
<name>Institution11</name>
</Institution>
<Institution>
<name>Institution12</name>
</Institution>
</Award>
<Award id=2>
<Institution>
<name>Institution21</name>
</Institution>
</Award>
---
Any way, you need to know the usage of tAdvancedFileOutputXML.
Best regards

shong

[Anonymous]

Thanks Shong. I always see that the Set As Group Element Option is disabled, I am using TOS version 3.0.0. Any clue ?

[Anonymous]

Hello

I always see that the Set As Group Element Option is disabled, I am using TOS version 3.0.0. Any clue ?

You must define a loop element first, after you define a loop element, one of elements before the loop element can be set as a group element.
Best regards

shong

[Anonymous]

I see the group element enabled just before the loop element now, thanks for that.
We have a need to have multiple loop elements in our XML file to represent the relation like below:
1. An award can have 1 or more Element codes
2. An award can have 0, 1 or more Reference codes
3. An award can have 1 or more institutions
3. An award can have 1 or more citations
------
and our XML structure is like below:
<?xml version="1.0" encoding="UTF-8"?>
<Award>
<AwardID>AwardID</AwardID>
<AwardTitle>AwardTitle</AwardTitle>
<AwardInstrument>AwardInstrument</AwardInstrument>
<AwardAmount>AwardAmount</AwardAmount>

---

<Institutions>
<Institution>
<Name>Name</Name>
<CountryName>CountryName</CountryName>
<StateCode>StateCode</StateCode>
<ZipCode>ZipCode</ZipCode>
<PhoneNumber>PhoneNumber</PhoneNumber>
<EmailAddress>EmailAddress</EmailAddress>
</Institution>
</Institutions>

<ProgramElements>
<ProgramElement>
<Code>Code</Code>
<Text>Text</Text>
</ProgramElement>
</ProgramElements>

<ProgramReferences>
<ProgramReference>
<Code>Code</Code>
<Text>Text</Text>
</ProgramReference>
</ProgramReferences>

<Citations>
<Citation>
<JournalTitle>JournalTitle</JournalTitle>
<JournalYear>JournalYear</JournalYear>
<JournalVolume>JournalVolume</JournalVolume>
<CitationUrl>CitationUrl</CitationUrl>
<ArticleTitle>ArticleTitle</ArticleTitle>
<AuthorIndicatorCode>AuthorIndicatorCode</AuthorIndicatorCode>
</Citation>
</Citations>

-----

</Award>
Since multiple looping is not permitted in tAdvancedFileOutputXML component, what is the best way to achieve this using Talend. Do you think we will need to write some custom code or can this be achieved using any other way in Talend (3.0.*) ?
Appreciate your quick help.

[Anonymous]

The append mode is a new feature for tFileOutputAdcancedXML available from the 3.1.x version is very interesting to make a multiple loop and write data into an XML file.

[Anonymous]

Thanks Cantoine. I installed TOS 3.1.2 and now see the 'Append the source XML' check box on tAdvancedFileOutputXML component. I couldn't find any documentation on how to use it, would you have any sample/documentation on how this can be used to generate an XML with multiple loop elements. I am basically trying to generate an XML with the structure above. Any suggestion would be really helpful.