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

Difference between in process and inter process?

0 Answers  

---

what is difference between migration project and integration project? can anyone explain with the example Thanks in advance

2 Answers   Satyam,

---

what is usage of datastage with materialized views

4 Answers   HP, TCS,

---

What is APT_DUMP_SCORE?

2 Answers   IBM,

---

What is a quality stage?

1 Answers  

---

---

Hai..,in datastage how to explain project in interview?please explain any domain please.

1 Answers   Wipro,

---

How much data u can get every day? 2)which data ur project contains? 3) what is the source in ur project?what is the biggest table & size in ur schema or in ur project?

2 Answers   Wipro,

---

How you Implemented SCD Type 1 & Type 2 in your project?

0 Answers  

---

1)i put Pharma Project in my Resume..whar are the sources used in my project Generally? 2)how many fact and dimensional tables used? 3)Have u used any Datamarts and measues in fact table? ....plz give the answers...

2 Answers   iGate,

---

HOW CAN WE SEE THE DATA IN DATASET?

12 Answers   IBM, TCS,

---

3) Sequential file contains data like Empno ename sal 111 abc 2000 Trgt file: Trgt1----111 Trgt2----abc Trgt3---2000

2 Answers  

---

my soure table is emp having columns sal,deptno in the deptno 10,20,30deptno row are there expected out put is min(sal) of 10th deptno,max(sal) of 20th deptno,mean(sal) of 30th deptno using aggregation stage

4 Answers  

---