Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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.
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
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.
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.
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: