Seven Stars

Parent-Child relationship in Talend

Facing problem and out of ideas on figuring on how to implement parent-child relationship in Talend.

Problem Statement:

Having a feed file which has data in below format

MemberCode|LastName|FirstName
A|SHINE|MICHAEL 
B|SHINE|MICHELLE 
C|SHINE|ERIN 
A|RODRIGUEZ|DAMIAN 
A|PAVELSKY|STEPHEN        
B|PAVELSKY|TERESA

(there are many more columns and many more rows - just few rows for reference purpose). LastName and FirstName are self-explanatory. MemberCode denotes the relationship. A will be parent, B or C will be child. For a certain employee record the data will always be in sequential manner - meaning the complete parent-child data will be in continuous rows.

Expected Result:

The above data needs to be outputed in below format:

  MemberCode|MemberLastName|MemberFirstName|DependentLastName|DependentFirstName
A         |SHINE         |MICHAEL        |                 |                  
B         |SHINE         |MICHAEL        |SHINE            |MICHELLE          
C         |SHINE         |MICHAEL        |SHINE            |ERIN              
A         |RODRIGUEZ     |DAMIAN         |                 |                  
A         |PAVELSKY      |STEPHEN        |                 |                  
B         |PAVELSKY      |STEPHEN        |PAVELSKY         |TERESA            

What I have tried so far:

The Talend job is having these components: tFileInputDelimited->tMap->tLogRow And tMap has the below logic -enter image description herewhich gives me output like below -

MemberCode|MemberLastName|MemberFirstName|DependentLastName|DependentFirstName
A         |SHINE         |MICHAEL        |                 |                  
B         |              |               |SHINE            |MICHELLE          
C         |              |               |SHINE            |ERIN              
A         |RODRIGUEZ     |DAMIAN         |                 |                  
A         |PAVELSKY      |STEPHEN        |                 |                  
B         |              |               |PAVELSKY         |TERESA

How to replicate the value for MemberFirstName and MemberLastName for MemberCode A for the rows having MemberCode B or C. Thanks in advance.

Platform: Talend Open Studio for Data Integration Version: 6.5.1

4 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Parent-Child relationship in Talend

Using a sequence based on LastName as key you can number each record and push the result to a tHashOutput.
Reuse this hashmap using à linked tHashInput as main row to a tMap (excluding number = 1).
Reuse it again as a lookup (excluding number > 1) and use an inner join to link both flows in the tMap.
Now you have what you need to produce the expected result.

TRF
Nine Stars

Re: Parent-Child relationship in Talend

Hi,

 

If you are looking for the following output, try below

 

ParentChild.PNGParentChild1.PNG

Regards,

Veeru Boppudi
Seven Stars

Re: Parent-Child relationship in Talend

Thank you for the solution. The outcome was a bit off, nevertheless the approach that you had suggested helped me in deriving to my desired result. 

Changed the tMap like this:

tMap-PC2.JPG

Seven Stars

Re: Parent-Child relationship in Talend

Thank you for your time and explanation. I tried to implement the job as you suggested and progressed a bit but stuck at the point on how to determine the parent-child relationship and use the same in tMap to output it successfully. Means - in tMap we now have Parent's from one row and Child's from another but struggling to map to the correct data. 

I tried like this -

tMap-PC3.JPG

I hope I have followed the steps as you have suggested.