Hi guys,
please design job for this,
MY INPUT IS
COMPANY,LOCATION
IBM,CHENNAI
IBM,HYDRABAD
IBM,PUNE
IBM,BANGLOORE
TCS,CHENNAI
TCS,MUMBAI
TCS,BANGLOORE
WIPRO,HYDRABAD
WIPRO,CHENNAI
HSBC,PUNE
MY OUTPUT IS
COMPANY,LOCATION,COUNT
IBM,chennai,hydrabad,pune,banglore,4
TCS,chennai,mumbai,bangloore,3
WIPRO,hydrabad,chennai,2
HSBC,pune,1
Thanks
Answers were Sorted based on User's Feedback
Answer / ankit gosain
Hi All,
Create a job design like below:
SeqFile--->SortStage--->Transformer--->RemoveDup--->SeqFile
Steps:
-----
1. At sort stage, take sort key = Company and sort key mode
= Don't sort (Previously Grouped) & take a
CreateClusterKeyChange column.
2. At Transformer Stage, create two stage variables:
temp of integer type with 0 as default,
temp1 of varchar type.
now, write in their derivation:
if clusterKeyChange=1 then 1 else temp+1----temp
if clusterKeyChange=1 then Location else temp1:',':Location-
---temp1
Create one o/p column (say count).
Now derive the o/p derivation columns as:
Company--------Company
temp1----------Location
temp-----------Count
3. At remove duplicate stage, take key=Company and
Duplicate to retain = Last
now just drag and drop the i/p columns to o/p derivation
& you will get the desired result.
For further queries, mail me on ankitgosain@gmail.com
Cheers,
Ankit :)
Is This Answer Correct ? | 14 Yes | 0 No |
what Ankit explained is correct. but small correction:
as per abouve explanation we get o/p location as below:
LOCATION
,chennai,hydrabad,pune,banglore
,chennai,mumbai,bangloore
,hydrabad,chennai
,pune
So, we need to do LTRIM/TRIM Function in devivation of the
column:
TRIM(temp1,',')---------->Location
then we get desired output.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / raj
There are two requirements here
1. Counting
2. Converting rows to columns
Here is the design of the job
Source -> Pivot(Vertical) -> Transformer -> Destination
Pivot:
1. Use vertical pivot.
2. Mention Company as Group by column and Location as pivot. In the aggregate column choose count
Transformer:
1. Above pivoting gives locations in different columns. Use transformer to concatenate these columns using : operator
Is This Answer Correct ? | 0 Yes | 0 No |
how can i abort the job if i get more than 100 errors in job log?
I have a scenario like Deptno=10---->First record and last record Deptno=20---->First record and last record Deptno=30---->First record and last record I want those first and last records from each department in a single target. How to do this in DataStage, any one can assist me. Thanks in advance.
i have a scenario in which i/p columns areID,salary with 1,1000 2,2000 and 3,3000 i need an extra column in the o/p named avg(salary)how can i get it?
How u implement the slowly changing dimensions if my source table is consisting of cid,cname,add,phno,email but i need to capture the changes for first three columns how u implement?
Difference between the Sequence and Sequencer in DS?
when u face critical situation in ur project, how to solve them?
in sequtial file 2 columns avaliable, i want only one column load the target. for this we can do by modify and copy stage. But here when using modify stage (in property drop column1) until it is ok. if target is data set How to view the data. with out using data management. what is the reason for this. if any body know this answer plz tel me. thanks.
1.What is a delta record? How you will read it? 2.What are the different types of sorting?
I/P --- ID Value 1 AB 2 ABC 3 ADE 4 A O/p --- ID Value 1 A 1 B 2 A 2 B 2 C 3 A 3 D 3 E 4 A
Explain the importance of surrogate key in data warehousing?
Can you define merge?
How many types of sorting methods are available in datastage?