Source Like
department_no, employee_name
----------------------------
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S
and Output should be like this
department_no, employee_list
--------------------------------
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S
- 3 Answers
- 8797 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
small modification to the above ans:
SRC---->Transformer--->TGT
we do the 'HASH' partitioning on Dept_No.
SetNull value to the Stage variable----->SV_List
In the transformer stage variables:
SV_List :',' : Employee_Name ----->SV_List (Concatenating Employee
Names)
In the Column Derivation:
TRIM(SV_list, ',' , 'L')------->Employee_Name
| Is This Answer Correct ? | 5 Yes | 1 No |
Answer / srinivas
By using the stage variables in transformer we can do this.
Before going to use transformer sort the data based on two columns.Order should be like below
Deptno Empname
10 A
10 B
SO ON...
After that in transformer take 3 stage varibles
stg1--->map the sorted Deptcolumn
stg2---> DSLink2.empname:',' (map the empname column and concatenate comma operator)
Stg2--->stg3:stg2 (concatenate first two staging varibles)
Then
In derivation of employee_list column (In transformer)
use below function
TRIM(stg3,',','T')---->employee_list (column derviarion)
It will gives you as expected result.
Thanks
Sri
| Is This Answer Correct ? | 5 Yes | 3 No |
SRC---->Transformer--->TGT
we do the 'HASH' partitioning.
SetNull value to the Stage variable----->SV_List
In the transformer stage variables:
SV_List : Employee_Name ----->SV_List (Concatenating Employee
Names)
In the Column Derivation:
SV_list------->Employee_Name
| Is This Answer Correct ? | 3 Yes | 1 No |
I have source like "abcdefghijklmnopqrs", how to change 10th character from j to z in datastage?
how to call sequential generator in datastage?
in sequtial file 2 columns avaliable,iwant only one column load the target how do it.
Hi All, Could you please let me know whether Datastage server and px supports oredb? OREDB:It's a Oracle Retail Embedded Database.Previousely It was called as Acumate data base. It's a multidimensional database. Please help me on this issue ASAP. Thanks in advance Ashok
how can we create a Sorrogate key in transformer stage? I want it in parallel mode
i have 3 diffrent tables. 1) US rate data 2)CANADA rate data and 3)MEXICO rate data. All 3 tables have 6 collumns each. 4 collumns are commun to all tables and 2 are diffrent. Now at target i want single table say Country rate which will have (4+2+2+2+1 flag) 11 collumns. I will add a flag collumn which will indicate country and will put nullable collumns which are not common to other. How i can implement this in datastage?
Can anyone tell me a difficult situation who have handled while creating Datastage jobs?
i want anser this question empno,ename,sal 12,mmm_ww,200 13,nnn_xx,300 14,bbb_qq,400 which stages are take which types of logicks are doing pls help me
i have 4 jobs i want run 1job should run on 1node and 2job runon 2node and.... how to make it possible?
What are the processing stages?
what is the difference between the active datawarehouse and datawarehouse
if a column contains data like abc,aaa,xyz,pwe,xok,abc,xyz,abc,pwe,abc,pwe,xok,xyz,xxx,abc, roy,pwe,aaa,xxx,xyz,roy,xok.... how to send the unique data to one source and remaining data to another source????
Teradata 
