Sort or rank a multi dimension table

[Hamid_Reza_Rashidi]

Hi every body,

please be informed that i have following table, 

Ord_Mah	Tech_No	ORD_QTY	Sup_Gro
Aza	YG2VQ	5	TRA
Aza	YG2LB	132	TRA
Aza	YG299	5	TRA
Aza	YG302	84	TRA
Aza	10001	45	TRA
Aza	11201	2	SBSA
Aza	11202	1	SBSA
DAY	YG2VQ	10	TRD
DAY	YG299	5	TRD
DAY	11138	120	SBSD
DAY	12023	2,880	PCD
DAY	11201	117	SBSD
DAY	11202	60	SBSD
DAY	YG2LB	2	TRD

I am going to give a grade the Tech_No. based on Ord_QTY in each Sup_Gro as followings:

The maximum value ,  100%

The second highest value  100%

The third highest value 90%

The 4th, 5th & 6th highest value 80 %

and ....

like following  attached table (photo), is a manual table including rank of each Tech_No 

the Last table should be like :

Ord_Mah	Tech_No	ORD_QTY	Sup_Gro	Rank
Aza	YG2VQ	5	TRA	80%
Aza	YG2LB	132	TRA	100%
Aza	YG299	5	TRA	80%
Aza	YG302	84	TRA	100%
Aza	10001	45	TRA	90%
Aza	11201	2	SBSA	100%
Aza	11202	1	SBSA	100%
DAY	YG2VQ	10	TRD	100%
DAY	YG299	5	TRD	100%
DAY	11138	120	SBSD	100%
DAY	12023	2,880	PCD	100%
DAY	11201	117	SBSD	100%
DAY	11202	60	SBSD	90%
DAY	YG2LB	2	TRD	90%

Best wishes

[Anil_Babu_Samineni]

Is there any logic behind for Rank? 

[Hamid_Reza_Rashidi]

First thanks for considering my case.

For sure, i am going to use this percentages in calculation of some other fields.

[Anil_Babu_Samineni]

I understand that part, But how 80% is calculated in first row for rank is my question.

[Hamid_Reza_Rashidi]

Hi,

would you please check the main question again.

there is three table there.

My table source is first table.

Please check the second table.

it is a pivot table of the source table.

                    (Rows) based on Ord_Mah & Tech_No 

                     (Column) based on Sup_Gro including (PCD,SBSA, SBSD,TRA,TRD)

                      Please check the columns,  SBSD & TRA, these are the examples. 

                     i want to have a new column sort the data (from max to min) and give

                     100% to max and second one.

                       90% to third highest value and ... 

for example in last table first row as following

Ord_Mah	Tech_No	ORD_QTY	Sup_Gro	Rank
Aza	YG2VQ	5	TRA	80%

Ord_Mah is Aza (please check the second table), column TRA, as 5 is the third highest value, the Rank was 80%

hope , my descriptions be fully clear.

i am waiting for your reply or any other question.

Best wishes.

[Anil_Babu_Samineni]

Sorry, It took longtime to see this. anyway, If that is not resolved can you please us to understand your logic?

Ex:

Load * Inline [

Name, Sales

A, 10

B, 20

C, 30

];

Output is this:

Name, Sales, Rank

A, 10,  3

B, 20, 2

C, 30, 1

Because, Rank column need to be calculated w.r.t the Sales of rank which is from max to min. 

Note: You can explain only one row or 2 rows to make understand clear