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

How a source file is populated?

1049

---

Can you explain how could anyone drop the index before loading the data in target in datastage?

1489

---

Notification Activity

1389

---

Hi,can any one please mention list of dimension and fact tables for Sales and Distribution System for a Pharmacutical company.

2277

---

There are two file are there .1st file contains 5 records and 2nd file contain 10 records in target they want 50 records.how can achieve this

2232

---

What is the purpose of pivot stage?

1155

---

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

1028

---

Can you explain kafka connector?

1290

---

What are some prerequisites for datastage?

1078

---

Why do we use exception activity in Datastage?

1288

---

in oracle target stage when we use load option and when we use upsert option?

2262

---

Difference between in process and inter process?

1211

---

Field,NVL,INDEX,REPLACE,TRANSLATE,COLESC

1398

---

How can you write parallel routines in datastage PX?

1001

---

Nls stands for what in datastage?

1182

---