provide logic to find rank

[vsandu]

Hi
woul you please provide logic to find rank on scr column by using transformer stage

This is my in put data

cid name scr
101 xxx 95
102 yyy 80
103 vvv 91
104 ddd 40
105 sss 92
106 eee 50

require o/p
cid name scr rank
101 xxx 95 1
102 yyy 80 4
103 vvv 91 3
104 ddd 40 6
105 sss 92 2
106 eee 50 5

[vamsi.4a6]

If the source is oracle you can user analytical functions directly in the query and if it is file you can follow the below steps

1)sort the data using sort stage on scr column (desc ) in sequential mode

2)In transformer use same partition and also counter=counter+1 intialized to 0

and also it won not assign same rank if scr column contains duplicates

[zulfi123786]

2)In transformer use same partition and also counter=counter+1 intialized to 0

Would not work when transformer is running on multiple nodes.

[vamsi.4a6]

Please correct me if i am wrong and i am a beginner for Datastage

1)since sort stage we are running in sequential mode(single node) and if we use same partition(without doing any repartition) in transformer then the transformer will run in sequential mode(single node) irrespective of number of nodes in configuraton file.

[ray.wurlod]

Not true. You have to change the Transformer stage properties explicitly to have it run in sequential mode (another way is to force the entire job to run in sequential mode, or to use a one-node configuration file).

Rank is not really an ETL function - it's a display function. That said, it can be done with stage variables, though you have to maintain two counts to handle the situation that you have tied ranks.

[vamsi.4a6]

1)Please let me know What is the problem if we use with same partition in transformer?

2)Suppose if are using same partition in transformer what data will contain in node1,node2,node 3 etc? beacause according to my understanding only node 0 will contain the data if we use same partition in transformer

of course i got your point that Transformer stage properties explicitly should be changed to run in sequential mode to get the correct output

[vsandu]

If the source is oracle you can user analytical functions directly in the query and if it is file you can follow the below steps

1)sort the data using sort stage on scr column (desc ) in sequential mode

2)In transformer use same partition and also counter=counter+1 intialized to 0

and also it won not assign same rank if scr column contains duplicates

Hi
i am not able to understand will u please elobarate please
what is counter= counter + 1
y it is require

[vamsi.4a6]

1)In stage variable define counter=counter+1 with intial value 0

and in Transfomer output derivation assign counter--->rank

2)After sorting during processing of first record in transformer counter=0+1 will contain 1

3) For second record counter=1+1[previousley counter value is 1] will contain 2 etc

As ray suggested use transformer in sequential mode instead of same paritition

[vamsi.4a6]

Not true. You have to change the Transformer stage properties explicitly to have it run in sequential mode (another way is to force the entire job to run in sequential mode, or to use a one-node conf ...

Can anybody please clarify the below points

1)Please let me know What is the problem if we use with same partition in transformer?

2)Suppose if are using same partition in transformer what data will contain in node1,node2,node 3 etc? beacause according to my understanding only node 0 will contain the data if we use same partition in transformer

of course i got your point that Transformer stage properties explicitly should be changed to run in sequential mode to get the correct output

[kandyshandy]

Vamsi,

SAME partition brings the data of 0 partition of the previous stage to 0 partition of current stage. This is used in most of the places where you don't need to reparitition the data flow.

This answers both of your questions.

E.g.

your job design is like dataset to transformer

Assume that 0 partition of dataset has

1,a
2,a
3,a

Then, 0 partition of transfomer will have

1,a
2,a
3,a

[vamsi.4a6]

I got your point
For this post to acheive the output i am following the below two steps

1)sort the data using sort stage on scr column (desc ) in sequential mode

2)In transformer use same partition and also counter=counter+1 intialized to 0


what is the problem with same partition in transformer since i am using sort stage in sequential mode and what data will contain in node1,node2,node 3 etc in transfomer?

[prasson_ibm]

Since your sort stage is running in sequential mode,so in case you apply same partition in transformer,it will run sequenctially.In transfromer all data will contains in node0 probably.


Thanks
Prasoon

[vamsi.4a6]

According to my knowledge i also understood same thing but ray.wurlod suggested to change the Transformer stage properties explicitly to sequential mode to get the correct output that point i did not understand what is the need for changing it?

[priyadarshikunal]

if one of the stage is running in sequential mode and then the downstream operator is running in parallel mode then the data is partitioned. set the transformer to run in sequential mode as keeping it in parallel is not going to add any value anyways.

[vamsi.4a6]

can i use same partition in transformer?If not what is the reason?