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?

Answers were Sorted based on User's Feedback



Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / 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 ?    7 Yes 1 No

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / shar

If you think ur solution is correct just try out with below
input and recheck ur solution..
class_id,Marks
101,50
101,60
101,60
101,50
101,40
102,90
102,90
102,35
102,35

Is This Answer Correct ?    4 Yes 0 No

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / indu

Input->Sort1->Sort2->Trnsformer->Output

Sort1-->Declare class_id and Marks as key column and sort
in descding order.
Sort2-->Declare class_id and Marks as key column(Sort Mode
to Dont sort previously sorted for both) and set clustered
key change column to true.

Otuput for Sort 2 will be

class_id Marks Rank
102 90 1
102 35 0
101 60 1
101 50 0
101 40 0

In the Transformer declare stage variable temp and
initialize to o

Derive temp--> If Rnak=1 then Rank else temp+1

Derive output columns as --->

class_id ---> class_id
Marks ----> Marks
Rank----> temp

Is This Answer Correct ?    13 Yes 10 No

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / 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

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / 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

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / abhay agrawal

Source--Tx--Target

In  Tx have 5 Stage Variables:
PreSID
CurrSID
PreMarks
CurrMarks
Rank

CurrSID=>PreSID
Input Sid=>CurrSID
CurrMarks=>PreMarks
Input Mark=>CurrMarks
If CurrSID=PreSID then If CurrMarks=PreMarks then Rank Else Rank+1 Else 1=>Rank

Take Rank as output column, let me know if any clarificationneeded.

Is This Answer Correct ?    0 Yes 0 No

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / anki_sri

Src>> Sort1 >> Sort2 >> Transfrmr >> Trgt

Sort1 --> sort with class_id and marks as well.
Sort2 --> declare key as id and select Dont Sort(Previously Sorted) and set create cluster key change column as True.

Transfrmr --> set two stage variables.
StageVar1=If Clusterkeychange=1 then Clusterkeychange else StageVar+1

StageVar=StageVar1
Create a new row as rank in transfrmr output and map StageVar1 to rank

Is This Answer Correct ?    3 Yes 8 No

Hi Friends, I have a input data like, class_id Marks 101 50 101 60 101 ..

Answer / amulya kumar panda

you are use
transformer if (Marks > 50 or 40 <Marks ) then rank=2 else
if Marks > 60 then rank=1 else rank=4

Is This Answer Correct ?    1 Yes 7 No

Post New Answer

More Data Stage Interview Questions

source has 2 fields like COMPANY LOCATION IBM HYD TCS BAN IBM CHE HCL HYD TCS CHE IBM BAN HCL BAN HCL CHE LIKE THIS....... AND I WILL GET THE OUTPUT LIKE THIS.... Company loc count TCS HYD 3 BAN CHE IBM HYD 3 BAN CHE HCL HYD 3 BAN CHE PLZ SEND ME ANSWER FOR THIS QUESTION..........

3 Answers   Patni,


when we have to go for a sequential file stage & for a dataset in datastage?

1 Answers  


In Sequential file, how can i split a column into two, and that column contains string datatype. For Example, i have column of string datatype as subedar khaja. Now i want get output as separately with subedar in one column and khaja in second column. How? Coula anybody, solve it?

2 Answers   Polaris,


Highlight the main features of datastage?

0 Answers  


What are routines in datastage?

0 Answers  






i have seq file that contents 10 million records load to target any data base.. in that case it takes lot of time for loading..how do performance tuning in that situation...?

5 Answers   TCS,


for example You have One Table with 4 Columns (Mgr ID, Department ID, Salary, Employee ID). Can you find out the Average Salary and Number of Employee present per Department and Mgr

0 Answers  


Hi this madan, in data stage one file in Empno 12345678910 in a table, i want target is  Empno 1                                2 3 4 5 6 7 8 9 10                                                                                             

5 Answers   Tech Mahindra,


What is apt_config in datastage?

0 Answers  


Demonstrate experience in maintaining quality process standards?

0 Answers   iGate,


What are the different type of jobs in datastage?

0 Answers  


What is meta stage?

0 Answers  


Categories