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

One Star

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

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.
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

Thanks Shong, it works great
One Star

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

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>
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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.
Employee

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

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.
One Star

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

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.
One Star

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

Thanks Shong and Cantoine for all your esponses so far. It would be really helpful if i can get your thoughts on whether i can use the AdvancedXML component in Talend 3.1.2 (in append mode) to generate the XML with multiple loop elements as seen in the structure above. If not, i believe i will have to quickly look at alternate options including writing some custom code.
Any quick suggestion on this is appreciated.
Community Manager

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

Hello
Yes, 'append the source xml file' option is available. For example:
There exists one xml file:

<rootTag>
<persons>
<person>
<id>1</id>
<name>shong</name>
</person>
<person>
<id>2</id>
<name>elise</name>
</person>
<person>
<id>3</id>
<name>haina</name>
</person>
</persons>
</rootTag>

Now, I will append another loop element to the xml file, see my screenshots.
in.csv:

1;22.23
2;32.12

Result:

<rootTag>
?
<persons>
<person>
<id>1</id>
<name>shong</name>
</person>
<person>
<id>2</id>
<name>elise</name>
</person>
<person>
<id>3</id>
<name>haina</name>
</person>
<item>
<id>1</id>
<price>22.23</price>
</item>
<item>
<id>2</id>
<price>32.12</price>
</item>
</persons>
</rootTag>

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

Siva et al:
This is a critically important scenario for us as well. Did you get this working? The "append" option appears to be a bit of a misnomer, in that it seem to allows you to insert new elements wherever you have a loop element defined. This is the key question: Does the append option allow arbitrary, complex elements to be added anywhere in the XML tree? Or would another approach be more robust, such as using tJavaRow, and leveraging either the DOM or, easier still, a Java XML data binding tool (Castor, JAXB, Liquid Tech's, etc.).
Opinions?
One Star

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

Hi,
I have written a job to fetch the data from the DB and if I use the tFileOutputXML then my o/p XML format will be
<xml>
<Person>
<id>P12</id>
<lastName>kumar</lastName>
<firstName>Arif</firstName>
<firstName>Shyam</firstName>
<deatils>
<name>hnlk1</name>
<value>1678</value>
</deatils>
<deatils>
<name>hnlk2</name>
<value>1878</value>
</deatils>
<deptId></deptId>
</Person>
</xml>

and if I use tAdvancedFileOutputXML then my Output XML format was
<xml><Department><deptId>1</deptId><deptName>ECE</deptName><deptHeadName>nikhil</deptHeadName></Department></xml>
I want to append the source XML so I am using the tAdvancedFileOutputXML, Is there any way to get the O/P XML in the 1st format by using tAdvancedFileOutputXML component?
Regards,
Pratap G.