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 :)

for example You have One Table with 4 Columns (Mgr ID, Department ID, Salary, Employee ID). Can you find out the Average Salary and Number of Employee present per Department and Mgr

1292

---

i WANTED TO USE THE RANGE LOOKUP SCENARIO IN DATASTAGE 7.5.2 SRVER JOB.i HAVE A DATE FIELD IN SOURCE AND I SHOULD MATCH IT WITH A FIELD IN LOOKUP FILE.BUT,THE FIELDS SHOULD MATCH EVEN THOUGH THERE IS SOME RANGE.CAN SOMEONE TELL ME HOW CAN I DO THAT. THANKS

3698

---

Hi everyone,I have kept a project Sales And Distribution for a pharmaceutical company.can anybody explain one complex business rule that we had in our project and how did you accomplish it using DS?

2161

---

Notification Activity

1345

---

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

2042

---

Hi All , in PX Job I have passed 4 Parameters and when i run the same job in sequence i dont want to use those parameters , is this possible if yes then how

1504

---

What are the types of jobs we have in datastage?

1019

---

What is ibm datastage flow designer?

1078

---

Explain how a source file is populated?

1308

---

What are the steps required to kill the job in Datastage?

1148

---

What are the differences between datastage and informatica?

1109

---

What are some prerequisites for datastage?

1037

---

Can you explain engine tier in information server?

1059

---

What is a merge in datastage?

1198

---

whom do you report?

1879

---