eno ename
1 qaz
1 wsx
1 edc
2 zxc
2 asd
3 qwe
3 wer
3 tru
4 rgj
Output:
eno ename count
1 qaz,wsx,edc 3
2 zxc,asd 2
3 qwe,wer,tru 3
4 rgj 1
I want the above output to be solved by DataStage as well and I have to write SQL query for the same output.
Answers were Sorted based on User's Feedback
Answer / himanshu jain
you can achieve this with AGGREGATOR and PIVOT ENTERPRISE stage in DATASTAGE Designer. First get count by using AGGREGATOR stage and then do Pivoting.
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / purba
For Datastage:
I/p........sort stage(create key change column for empno.)........ transformer ( here take stage variables count=0,sv1. For sv1 give derivation as(if keychange=1 then sv1=ename else sv1=sv1:',':ename) for count derivation (if keychange=1then count=1 else count=count +1).........remove duplicate stage(retain last)......o/p(desired result)
Is This Answer Correct ? | 1 Yes | 0 No |
Seq stg------>Sort stg--------> Trnsfrm stg-------->Tgt
1. read data in seq file stage.
2. In sort stage enable key column change as true.
3. In transformer stage define stage variable SV1 (if DSLink5.keyChange = 1 then 1 else SV1 +1) and SV2 (if DSLink5.keyChange = 1 then DSLink5.ENAME else SV2 : ',' : DSLink5.ENAME
) . In output tab link ENO, SV2 -> Ename and SV1 -> Count. Add a constraint LastRowInGroup(DSLink5.ENO) and mark the ENO as key in input column.
Is This Answer Correct ? | 0 Yes | 0 No |
Anyone has Datastage certification free dumps for 000-418 , 000-421 codes, mail me @ manik.dwh@gmail.com 000-418 : InfoSphere DataStage v8.0 000-421 : InfoSphere DataStage v8.5
Can you explain tagbatch restructure operator?
Difference between data warehousing and olap?
A table containg 100 records B table containg 20 records we have to join two tables in left outer it containg target 100 records but target containg 101 record at that time what is the issue arise
how to handle null values in sequential file?
What are the unit test cases you used in your project?
If you want to use the same piece of code in different jobs, how will you achieve it?
State the difference between an operational datastage and a data warehouse?
Notification Activity
How do u set a default value to a column if the column value is NULL?
I want capture UnMatched records from Primary source and secondary source in JOIN stage?
which dim, fact tables used in bank domain