topic Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup? in Talend Studio

How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Fri, 18 Jan 2019 02:44:13 GMT

Hi Folks,

I'd like to know your inputs on how to create a hierarchy from one source file without using too many tHashInputs as a lookup. See requirements and expected output below:

Input
|Emp Name | Emp Id | Manager Id|
|--------------------------------------------------|
|Jerome Sare | JNS12 | MR13 |
|Madey Den | MR13 | MB14 |
|Mike Bick | MB14 | TS15 |
|Trevlin Sucre | TS15 | DS16 |
|Dicker Sipeds | DS16 | VS17 |
|Vander Sicken | VS17 | VS17 |

CEO_LVL1 = Emp Id is equal to manager Id that will be CEO_LVL1, then it will traverse down up to 4 levels to get the CEO_LVL2 - CEO_LVL4.

Let me know your thoughts on this.

Thank you.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

akumar2301 — Fri, 18 Jan 2019 17:40:38 GMT

You could try tmap option to reload at each row to get heirarchy infomation.

Main — your input file (emp name,emp id, mng id,[newcol- list of manger])
Do reaload at each row ( global var on manager id)

Lookup - from inputfile filter record with (manager id of main ) = (emp id of lookup)

Ouput - main emp name , lookup manager id , concatinate (lookup emp name )with existing list of manager

Do this in tloop 4-5 times. Read ouput of tmap as input in loop.
Last cols will have your manager list.it should work.

Let me know if you face issue.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Mon, 21 Jan 2019 03:59:31 GMT

Hi Abhishek,

Thank you for your response. I'll give this a try, but I'm still confused on some points:

1. What will be the content of the new col on Main row of tMap?
2. Why I will concatenate (lookup emp name )with existing list of manager

3. What will be the option will I use in tLoop?

Can you provide an example for this?

Thank you in advance!

Best regards,

Jerome

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

akumar2301 — Mon, 21 Jan 2019 08:08:30 GMT

Last column will contain the CEO(Manager List). This hierarchy of Managers appended after each loop run.

e.g. for Jerome Sare
After 1st run last column will have Madey Den
After 2nd run last column will have Mike Bick,Madey Den
and so on.

I will try to implement and send you a sample job if i get time.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Mon, 21 Jan 2019 09:07:59 GMT

I'm also thinking if it is possible to loop records until Emp Id is equal to manager Id, then afterwards that's the time to traverse down up to 4 levels since some of the records have random number of levels before reaching the topmost level. But I'm not knowledgeable how to do that.
What I'm thinking is a series of lookups, but I know that it takes too much toll on the performance.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

akumar2301 — Mon, 21 Jan 2019 10:43:30 GMT

PFA the job for the same.

This is assuming you have only One one root Elements (TOP CEO). Hope this will help.

I did it quick so you could improve the design as per your use case.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Tue, 22 Jan 2019 08:28:40 GMT

Hi Abhishek,

Thank you so much for your inputs and the job, I'm getting the idea little by little, but I couldn't make it work. I encountered an error which is Missing file on the main row of tMap, I added +1 to make it work, but the loop doesn't stop. Also, it will generate several files from the loop which may cause a space issue.

Regards,

Jerome

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

akumar2301 — Tue, 22 Jan 2019 09:10:15 GMT

Ideally it should not give error with the input I attached.You might have to correct the path in INPUT and OUTPUT.

As I told , i did this quick , you could use Hash components to avoid having multiple file or you could delete file inside loop.

Main Logic is in tMap to build hierarchical output. Using that idea , you could improve the design. My output looks like this.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Wed, 23 Jan 2019 06:14:55 GMT

Thank you so much Sir!

It's now working. I am planning to replace the files inside the loop with hash components, but it affected the loop up to the point that it didn't end. I can delete the file as well, but it is needed on the 2nd subjob where the last file will be read.

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Fri, 25 Jan 2019 08:19:51 GMT

Thank you @uganesh . I replaced the delimiter from comma to pipe since my source is csv and has data with comma values.
But I am currently blocked how will I replace those I/O delimited with Hash components.

What I tried is to make it two subjobs...

1st subjob is basically the replica without the loop logic

while the 2nd job has the loop..setting the output as linked with the 1st hash but it's not working.
Can you please advice me?

Here's what the job looked like:

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Wed, 30 Jan 2019 08:35:57 GMT

@uganesh,

Here's the sample file and talend job that I modified based on the attachment you've provide.
Thank you very much.

ed_users_ceo_hier1.txt
Hierarchy_Copy.zip

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

akumar2301 — Wed, 30 Jan 2019 13:57:54 GMT

Find attached a simple job with Hash.
 
 

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

jerownimow — Thu, 31 Jan 2019 06:35:32 GMT

Thank you @uganesh, I tried executing the job and it doesn't go into loop like the ones you've provided me before. I'm currently in-progress with troubleshooting..Please advise.

Thank you so much!

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

Anonymous — Fri, 17 May 2019 05:33:18 GMT

@uganesh Can you please help me with https://community.talend.com/t5/Data-Quality-Preparation-and/Employee-Hierarchy-using-talend/td-p/161122

Re: How to create hierarchical output from one source file without using too many tHashInput as a lookup?

Anonymous — Fri, 24 May 2019 07:16:57 GMT

Where is the attachment