DSXchange

Source File: | Target File:
empno,empname,leadname | empno.empname,leadname,managername
101,John,Dave | 101,John,Dave,Nicholas
102,Dave,Nicholas | 102,Dave,Nicholas,
103,Tony,David | 103,Tony,David,Jennet
104,David,Jennet | 104,David,Jennet,

In the above example Dave is lead of John and Nicholas is lead of Dave so Nicholas is Manager of John. Similarly David is lead of Tony and Jennet is lead of David so Jennet is Manager of Tony.

Use the above logic and create a target file with managername also in target file.

Hi,

You will have to do self-join.
The first 3 columns will remain the same and in the third column you will do a lookup and see if there is any lead to the correspoding employee. And if there is then you will append with a comma.

For example,

empno | empname | lead name | manager name
101 | John | Dave | Nicholas(do a lookup on lead name and get it from table)
102 | Dave | Nicholas | (No name is found corresponding to Nicholas. So it will be blank)
103 | Tony | David | Jennet
104 | David | Jennet | (No name is found corresponding to Jennet. So it will be blank)

I hope this should work.

ok. but in which stages are used in this job

Do you have to worry about multiple levels of hierarchy or only ever 2 levels?

If you can stage this to a database, a recursive lookup would be easier (can't remember the formal name of it but in Oracle I believe it is CONNECT BY)

I'd do it all in user-defined SQL using the appropriate stage type to connect to your particular database.

You need to use Source, destination and LKP.


Source -----> Lookup(Transformer if you are building Server Job) -----> Destination


@Ray/Kryton - I thought that it could be done by query but wasn't sure how. Could you guys please show me the query.

Something like the following query should do it for you:

Thanks.....

As long as you only ever need to go two levels deep... which sounds like it may meet your requirement.