Interested to Buy Any Domain ? << Click Here >> for more details...
Hi Friends,
I have a input data like,
class_id Marks
101 50
101 60
101 40
102 90
102 35
And i want my output data like
class_id Marks Rank
101 50 2
101 60 1
101 40 3
102 90 1
102 35 2
how to do this in datastage?
- 8 Answers
- 28956 Views
- Cognizant, HCL, TIAA CREF, I also Faced
- E-Mail Answers
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 :)
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
Which warehouse using in your datawarehouse
Describe routines in datastage? Enlist various types of routines.
how to export or import the jobs in .ISX file
Describe link sort?
Demonstrate experience in maintaining quality process standards?
How to find value from a column in a dataset?
What could be a data source system?
Different ways a project can be moved to production ?...example ….export-import and Version control.
How can you write parallel routines in datastage PX?
In work load management there are three options of Low priority, Medium priority and High Priority Jobs which can be used for resource management. why this feature is developed when there is already jobs prescheduled by scheduler or autosys. what will be the use of workload management then?
On which interface you will be working as a developer?
disign the complex job in u r project?(they are aksing only complex job design and then data flow...)
What are the differences between datastage and informatica?
Define ds designer?
Differentiate between operational datastage (ods) and data warehouse?
