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

When we will go for Data elements?

3 Answers   TCS,

---

How complex jobs are implemented in datstage to improve performance?

0 Answers  

---

Can you explain engine tier in information server?

0 Answers  

---

Hi any one has datasatge 8.5 certication dump could you please provide your details i will contact you. send reply to my mail rajaadivi@gmail.com Thanks in Advance

2 Answers  

---

How one source columns or rows to be loaded in to two different tables?

0 Answers  

---

---

source contains 2 columns comes to target 4 columns how

1 Answers   IBM,

---

if we take 2 tables(like emp and dept), we use join stage and how to improve the performance?

5 Answers   Cap Gemini,

---

What are routines in datastage?

0 Answers  

---

What is the different type of jobs in datastage?

0 Answers  

---

Describe routines in datastage? Enlist various types of routines.

0 Answers  

---

Demonstrate experience in maintaining quality process standards?

0 Answers   iGate,

---

Distinguish between informatica & datastage. Which one would you choose and why?

0 Answers  

---