Answer Posted / ankit gosain

Hi friends,

This question is a trick and as i found in every answer
given previously, there is a slight miss-queue.

Check out my solution for this question:

I/p File(Primary)
!
I/p
file---->Sort---->Sort---->transformer---->join/lookup---->O/p
File

(1). In the first sort stage, make class_id and marks fields
as keys, sort by descending and then create a Key change column.

(2). In second sort stage, make class_id only as a key
(don't sort previously sorted) and create a cluster key
change column.

(3). You will get the o/p as given below:

Class_Id, Marks, KeyChange, ClusterKeyChange
102,90,1,1
102,35,1,0
101,60,1,1
101,50,1,0
101,40,1,0

(4). In transformer stage take a integer type variable say
var1 and create one column say Rank.
Now, derive Var1 as :
if ClusterKeyChange=1 then 1 else Var1
And, derive Rank as :
if ClusterKeyChange=1 then 1 elseif (ClusterKeyChange=0 and
KeyChange=0) then Var1 else Var1+1

(5). Take only class_id, marks & rank as an o/p of the
transformer, so that you will have the o/p as given below:

Class_id, Marks, Rank
102,90,1
102,35,2
101,60,1
101,50,2
101,40,3

(6). Now finally, apply join/lookup and take this o/p as a
Reference data and Primary data will be the Source I/p File.
Take Class_id and Marks as keys and derive Rank to the o/p,
so that you will get the finally o/p as given below:

class_id Marks Rank
101 50 2
101 60 1
101 40 3
102 90 1
102 35 2

If you have any other doubt, you can mail me at
ankitgosain@gmail.com

Cheers,
Ankit :)

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

1126

---

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?

2263

---

CHANGE CAPTURE

1355

---

What are the stages in datastage?

1138

---

Explain connectivity between datastage with datasources?

1043

---

Which warehouse using in your datawarehouse

2239

---

What is the sortmerge collector?

1177

---

Define Merge?

1374

---

What are the types of containers in datastage?

1227

---

What are system variables and sequencers in datastage

1183

---

Difference between IBM DATA STAGE8.5 and DATA STAGE9.1 ?

3853

---

What are transforms and what is the differenece between routines and transforms?

1109

---

1.new record it will insert but changes of natural key is not present in taget i want to update (here key is composite natural key )can any one help this to explan how to do

2141

---

How you can fix the truncated data error in datastage?

1887

---

How a source file is populated?

1115

---