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

[jerownimow]

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           |

Expected Output
Emp Name      | CEO_LVL1       | CEO_LVL2     | CEO_LVL3    | CEO_LVL4
-------------------------------------------------------------------------------------------------
Jerome Sare    | Vander Sicken | Dicker Sipeds | Trevlin Sucre | Mike Bick
Madey Den      | Vander Sicken | Dicker Sipeds | Trevlin Sucre | Mike Bick
Mike Bick         | Vander Sicken | Dicker Sipeds | Trevlin Sucre |
Trevlin Sucre   | Vander Sicken | Dicker Sipeds |                       |
Dicker Sipeds  | Vander Sicken |                        |                       |
Vander Sicken | Vander Sicken |                        |                       |

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.

[akumar2301]

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.

[jerownimow]

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

[akumar2301]

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.

[jerownimow]

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.

[akumar2301 · Accepted solution]

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.

[jerownimow]

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

[akumar2301]

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.

[jerownimow]

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.

[jerownimow]

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: