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
- 25322 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
What is the difference between account and directory options ?
What is the use of datastage director?
what should be ensure to run the sequence job so that if its get aborted in 10th job before 9job should get succeeded?
What is the method of removing duplicates, without the remove duplicate stage?
Name the different types of Lookups in Datastage?
hi iam new to this tooliam cmpltied to know abt datastage so now iam in project tell me whole step by step what iam doing iwnt to go with exp so plz hlp me pals
explain about completely flow of sequencers technicaly,without using example??explain about lookup,nullhandling?
What are the functionalities of link collector?
How do you import and export the datastage jobs?
Have you used Unstructured data?
Can you highlight the main features of ibm infosphere information server?
What are the types of views in datastage director?
Source has 2 columns: USA,NewYork INDIA,MUMBAI INDIA,DELHI UDS,CHICAGO INDIA,PUNE i want data in target like below: INDIA,MUMBAI1 INDIA,DELHI2 INDIA,PUNE3 USA,NEWYORK1 USA,CHICAGO2
Explain ibm infosphere information server and highlight its main features?
How to clean the datastage repository?
