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

Describe stream connector?

1292

---

Notification Activity

1388

---

What is the use of datastage designer?

1134

---

What is ibm datastage?

954

---

how can we create rank using datastage?what is the meaning of rank?

8758

---

hi.... am facing typical problem in every interview " I need some critical scenarios faced in real time" plz help me guys

2847

---

What are datastage sequences?

1100

---

DB2 connector> transformer > sequential file Data will be exported into a csv format in a sequential file. This file will be send in a email using a sequence job. Problem here is, how to avoid sending a blank csv file? When I ran the job there are chances that it might return zero records but in the sequence job csv file is going blank. how can I avoid this? thanks

1511

---

How do you import and export data into datastage?

1221

---

What are the difference types of stages?

1182

---

Explain ibm infosphere information server and highlight its main features?

1079

---

How to create a file using vi editor? 2)how to delete a file in vi editor? 3)How to connect the server datastage to unix? what r the command lines we r using? 4)30 jobs r runnig in unix i want to find out my job. how to do this? give me command?

2600

---

Differentiate between datastage and informatica?

1168

---

CHANGE CAPTURE

1292

---

if i have two tables table1 table2 1a 1a,b,c,d 1b 2a,b,c,d,e 1c 1d 2a 2b 2c 2d 2e how can i get data as same as in tables? how can i implement scd typ1 and type2 in both server and in parallel? field1 field2 field3 suresh , 10,324 , 355 , 1234 ram , 23,456 , 450 , 456 balu ,40,346,23 , 275, 5678 how to remove the duplicate rows,inthe fields?

2165

---