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
- 28307 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 are the processing stages?
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.
How one source columns or rows to be loaded in to two different tables?
What is a folder? Difference types of stages?
How do you remove duplicate values in datastage?
Define project in datastage?
Can you implement SCD2 using join, transformer and funnel stage?
How rejected rows are managed in datastage?
How can we perform the 2nd time extraction of client database without accepting the data which is already loaded in first time extraction?
What are the different plug-ins stages used in your projects?
What is the sortmerge collector?
Differentiate between data file and descriptor file?
What all are the different way to run a job?
1)what is the size of Fact table and dimension table? 2)how to find the size of Fact table and dimension table? 3)how to implement the surrogate key in transform stage? 4)write the configuration file path? 5)how many types of datasets explain? 6)diff b/w developed projects and migration projects? 7)how to delete the header and footer file of the sequencer file? 8)how can u call the parameters in DS in unix environment? 9) how much data ur getting daily ? 10)
if i have two tables table1 table2 1a 1a,b,c,d 1b 2a,b,c,d,e 1c 1d 2a 2b 2c 2d 2e how can i get data as same as in tables? how can i implement scd typ1 and type2 in both server and in parallel? field1 field2 field3 suresh , 10,324 , 355 , 1234 ram , 23,456 , 450 , 456 balu ,40,346,23 , 275, 5678 how to remove the duplicate rows,inthe fields?
