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 :)

Answer / subhash

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.

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

How many input links can you give to a Transformer stage?

6 Answers  

---

what is stage is used for below Input columns: dept|mgr|employee|salary Output columns: mgr|count of employee per mgr|avg salary per dept note: each dept has one mgr and each mgr has many employees

0 Answers  

---

what is diff b/w datastage 8.1,8.5,8.7?

1 Answers   IBM,

---

what is the use of surogate key in datastage

3 Answers  

---

Why we use parameters instead of hard code in datastage.

2 Answers   IBM,

---

How to Convert a string function to date function by using only sequential file stage with out using other stages ?

5 Answers   TCS,

---

What is the purpose of pivot stage and types of containers in datastage

0 Answers  

---

what r the sources u need to create the datastage?

1 Answers  

---

What is exact difference between Parallel Jobs and server Jobs..

7 Answers   IBM,

---

How can we move a DATASTAGE JOB from Development to Testing environment with the help of a datastage job using unix commands.

5 Answers  

---

options available in sequence job to run,validate?

0 Answers   CTS,

---

What is the command line function to import and export the ds jobs?

0 Answers  

---