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

eno ename esal acct1 acct2 amount1 amount2 100 suresh 10000 sbi1 sbi2 1000 2000 this is our sourse data i would loke to disply like this eno ename esal acct amount 100 suresh 10000 sbi1 1000 100 suresh 10000 sbi2 2000

2 Answers   HCL,

---

In one project how many shared containers are created?

3 Answers   NTT Data,

---

how to convert rows into columns

2 Answers   IBM,

---

WHERE YOU USE UNIX COMMANDS AS A ETL DEVELOPER?

2 Answers   Wipro,

---

How do you remove duplicate values in datastage?

0 Answers  

---

How to get max salary of an organization using data stage stages........... can any body help me plz.......

7 Answers   Cap Gemini,

---

if a column contains data like abc,aaa,xyz,pwe,xok,abc,xyz,abc,pwe,abc,pwe,xok,xyz,xxx,abc, roy,pwe,aaa,xxx,xyz,roy,xok.... how to send the unique data to one source and remaining data to another source????

3 Answers  

---

table actions available in oracle connector?

0 Answers   CTS,

---

Source has 2 columns: USA,NewYork INDIA,MUMBAI INDIA,DELHI UDS,CHICAGO INDIA,PUNE i want data in target like below: INDIA,MUMBAI1 INDIA,DELHI2 INDIA,PUNE3 USA,NEWYORK1 USA,CHICAGO2

0 Answers  

---

I am having the 2 source files A and B and I want to get the output as, the data which is in file A and which doesn't in file B to a target 1 and which is in file B and which doesn't in file A to a target 2?

3 Answers  

---

how do u convert date in 20-12-07 to dec-20-2007 or 20-dec- 2007 or 20-dec-07 in parallel

3 Answers   Wipro,

---

What are the repository tables in datastage?

0 Answers  

---