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 ..

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 ..

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 ? 4 Yes 0 No

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

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 ..

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 ..

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 ..

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 ..

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 7 No

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

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

More Data Stage Interview Questions

ename like ibm,tcs,hcl need display those records only how?

I have a file it contain 2 records like empname,company as Ram, Tcs and Ram, IBM. But i want empname, company1,company2 as Ram, TCS,IBM in the target. How?

My source having following data as below, AB1 Aim2 Abnv5 1An8bx and my question is i need the Datastage job the following as in my target 000AB1 00Aim2 0Abnv5 1An8bx Please help me to achive this.

1)How to Duplicate Records Delete in Sequential file?

what will happen if we allow duplicates in datastage lookup abort drop record 1st value of duplicate record none

explain how to create SCD-2 IN DATASTAGE 7.5X2 PLZ EXPLAIN WITH 4 OR 5 RECORDS TAKE IT EXAMPLE AND JOB DESINGN URGENT

How can i approach to write datastage 7.5 Certification? and how much they will charge for examination .What exactly should i do? Can anyone guide me plz?

Source flat file contains src --- 1 2 ' ' ' 18 we had 3 targets T1 T2 T3 -- -- -- 1 4 7 2 5 8 3 6 9 10 13 16 11 14 17 12 15 18 How can i get? **Using only datastage, but not unix or any other. I am expecting the answer soon.. Thanks in advance.

what is the best stage in datastage parller jobs to use full out join and why

how to cleansing data

How we can convert rows to columns in datastage?

deptno wise to find max and min,and sum of rows and in target to company wise maximum

Categories