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 is flow of project?

1559

---

On which interface you will be working as a developer?

639

---

How do y read Sequential file from job control?

14418

---

Give an idea of system variables.

589

---

What are the types of hashed files in data stage

646

---

---

What are the different type of jobs in datastage?

608

---

How to write a expression to display the first letter in Caps in each word using transformer stage ? Please let me know ASAP Thanks in advance...

1083

---

what is use of SDR function?

4710

---

how can we validate the flat files using the date in the header and number of records in the flat file? Using both conditions at a time.

2735

---

In Informatica,for the table I can find coreesponding dependent mappings.Likewise can I find the dependent jobs with all the information by using the table name

2014

---

What are the functionalities of link partitioner?

620

---

What is the use of datastage director?

590

---

Demonstrate experience in maintaining quality process standards?

1939

---

Which warehouse using in your datawarehouse

1682

---

Difference between in process and inter process?

701

---