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

What are orabulk and bcp stages?

0 Answers  

---

how can we extract data with out having any common column

7 Answers   IBM, Switch2iTech,

---

i have seq file that contents 10 million records load to target any data base.. in that case it takes lot of time for loading..how do performance tuning in that situation...?

5 Answers   TCS,

---

Define meta stage?

0 Answers  

---

What are the types of hashed files in data stage

0 Answers  

---

---

What is quality stage?

0 Answers  

---

what is the new version in Datastage ? what is the diff b/t New vesion & 7.5 version?

12 Answers   Wipro,

---

Wat is isolation level and when do u use them?

1 Answers   HP, IBM,

---

Drop duplicate records ... SOURCE LIKE .......... ID flag1 flag2 100 N Y 100 N N 100 Y N 101 Y Y 101 N Y 102 Y N 103 N N 104 Y Y 105 N N 106 N Y 102 N Y 105 Y Y in above file if any id having both the flags as "N" then that corresponding id records should be dropped, in above case o/p should be as ID flag1 flag2 101 Y Y 101 N Y 102 Y N 102 N Y 104 Y Y 106 N Y Steps to do : 1) Identified the id’s that got duplicated (both the flag values having vales “N”) 2) Look up with these id’s to existing id’s to drop .

2 Answers  

---

A signal has a wavelength of 1 micro min in air.how far can the front of the wave travel during 1000periods?

0 Answers  

---

How to reverse the string using SQL?

0 Answers   CTS,

---

How to Remove Duplicate using SQL?

0 Answers   CTS,

---