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

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

2295

---

1.what is repartionoing technique? 2.what deliverables transferred to client using datastage? 3.how to write loop statements using nested loop sequence?

2277

---

What are the types of containers?

1237

---

What is quality stage?

988

---

explain about citrix scheduling tool in datastage

2457

---

How to convert RGB Value to Hexadecimal values in datastage?

3999

---

Is it possible to query a hash file?

2012

---

Explain the importance of surrogate key in data warehousing?

1191

---

How do you import and export data into datastage?

1235

---

What is oci?

1302

---

What are the types of views in datastage director?

1775

---

What is datastage?

1084

---

Explaine the implimentation of scd's in ds indetail, please send me step by step procedure to perform scd's 1,2,3. Please replay for this, Thanks in advance

2224

---

Hi , Can anyone give few examples of scenarios and there corresponding design in datastage..i am new to this tool...confused in design while my manager asking to design the job.. Please post the URL if there..so i can go through it.. Thanks in advance...

4188

---

What are the job parameters?

1221

---