how to find ds logic
Moderators: chulett, rschirm, roy
how to find ds logic
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.
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.
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
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.
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.
Re: ok. but in which stages are used in this job
ok. but in which stages are used in this job
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Something like the following query should do it for you:
Code: Select all
select a.empno, a.empname, a.leadname, b.empname
from emps a left outer join emps b on a.leadname = b.empname
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.