hi my source is::
empno,deptno,salary
1, 10, 3.5
2, 20, 8
2, 10, 4.5
1, 30, 5
3, 10, 6
3, 20, 4
1, 20, 9
then target should be in below form...
empno,max(salary),min(salary),deptno
1, 9, 3.5, 20
2, 8, 4.5, 20
3, 6, 4, 10
can anyone give data flow in data stage for the above
scenario....
thanks in advance...
- 5 Answers
- 8423 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / lakshmi srinivas
source->copy->2 aggregators->join->target
1 aggregator->eno,max(sal),min(sal)
2 aggregator->eno,dno,max(sal)
by using max(sal) key, we can join both o/p of
aggregators,we can get that output...
| Is This Answer Correct ? | 13 Yes | 1 No |
the question framed wrong
it should have been
empno,dept,sal
1,10,9
1,10,8
1,10,7
2,20,7
2,20,8.5
2,20,9
3,30,4
3,30,6
then expecting an ans is correct
o/p
empno,max(sal),min(sal),deptno
1,9,7,10
2,9,7,20
3,6,4,30
---xx----
where as in the o/p of user's question
we have
empno,max(sal),min(sal),deptno
1,9,3.5,20
2,8,4.5,20
3,6,4,10
here empno=1 his max(sal)=9 from deptno 20 and min(sal)=3.5 then its deptno=? actually we r giving a correct information but client will get confused max(sal) and min(sal) r both from 20 or different departments.
-----xxx----
even though client is expecting the same output then laxmi answer is correct
| Is This Answer Correct ? | 0 Yes | 0 No |
hai first use the copy stage. from that take three links.
in that first and second links are connected to aggregator
stages.
agg1-- max and min for sal group by empno...
agg2--max only group by empno...
then use lookup for agg2 and third link of copy stage...
in lookup join max{sal} to sal and get deptno...
finally, o/p links from agg1 and lookup are joined by
lookup.. join by max{sal}....
then u can get the desired o/p...
| Is This Answer Correct ? | 0 Yes | 2 No |
Answer / eswararao
create one group using column generator and then Using Aggregator stage select max sal,min sal
| Is This Answer Correct ? | 1 Yes | 5 No |
Answer / pankaj das, orator
Just take...
source -> aggregate stage -> target
And then in aggregate stage in output tab just specify
col name groupby derivation
------- ------- ----------
id group by id
max_sal max(sal)
min_sal min(sal)
deptno deptno
| Is This Answer Correct ? | 1 Yes | 10 No |
1.what is repartionoing technique? 2.what deliverables transferred to client using datastage? 3.how to write loop statements using nested loop sequence?
Display all files which have size more 3KB in given directory/folder.
Differentiate between odbc and drs stage?
i WANTED TO USE THE RANGE LOOKUP SCENARIO IN DATASTAGE 7.5.2 SRVER JOB.i HAVE A DATE FIELD IN SOURCE AND I SHOULD MATCH IT WITH A FIELD IN LOOKUP FILE.BUT,THE FIELDS SHOULD MATCH EVEN THOUGH THERE IS SOME RANGE.CAN SOMEONE TELL ME HOW CAN I DO THAT. THANKS
Input Data is: Emp_Id, EmpInd 100, 0 100, 0 100, 0 101, 1 101, 1 102, 0 102, 0 102, 1 103, 1 103, 1 I want Output 100, 0 100, 0 100, 0 101, 1 101, 1 Means Indicator should either all ZEROs or all ONEs per EmpId. Impliment this using SQL and DataStage both.
Explain the functionality of your current project?
1 Answers IBM, Miles Software,
what is a message handler
ename like ibm,tcs,hcl need display those records only how?
is it possible to access the same job by two users at a time in DataStage?
What are the primary usages of datastage tool?
What are the functionalities of link partitioner and link collector?
what will happen if we allow duplicates in datastage lookup abort drop record 1st value of duplicate record none
Teradata 
