1. Categories >> Software >> Data Warehouse >> Data Stage
  2. Suggest New Category

Hi guys,

please design job for this,

MY INPUT IS

COMPANY,LOCATION
IBM,CHENNAI
IBM,HYDRABAD
IBM,PUNE
IBM,BANGLOORE
TCS,CHENNAI
TCS,MUMBAI
TCS,BANGLOORE
WIPRO,HYDRABAD
WIPRO,CHENNAI
HSBC,PUNE

MY OUTPUT IS

COMPANY,LOCATION,COUNT
IBM,chennai,hydrabad,pune,banglore,4
TCS,chennai,mumbai,bangloore,3
WIPRO,hydrabad,chennai,2
HSBC,pune,1


Thanks

Question Posted / ankit gosain

Answer Posted / 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 :)

Is This Answer Correct ?    14 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

How to read the length of word in unix?

882

What are the different common services in datastage?

705

What steps should be taken to improve Datastage jobs?

637

What are stage variables and constants?

700

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

610




What are the partitioning techniques available in link partitioner?

1753

8000 jobs r there i given commit, suddenly job will abort? what happens? 2)diff b/t transformer stage & filter stage? 3)how to load the data in the source?

1780

Describe the main features of datastage?

654

Which warehouse using in your datawarehouse

1680

i have a job with 3 possibiliies finish with ststus ok abbort warning how do you handle these 3 in job sequence? explain the situation where you have appiled scd in your project? chandu

2270

What are the areas of application?

658

What are the different plug-ins stages used in your projects?

623

What is developer responsibilities in UAT (user acceptance testing and Post implementation phase?

1635

What is merge stage?

760

Can you explain repository tables in datastage?

899