topic Re: Sort or rank a multi dimension table in QlikView

Sort or rank a multi dimension table

Hamid_Reza_Rashidi — Mon, 28 Dec 2020 10:36:16 GMT

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

Re: Sort or rank a multi dimension table

Anil_Babu_Samineni — Mon, 28 Dec 2020 11:27:08 GMT

Is there any logic behind for Rank?

Re: Sort or rank a multi dimension table

Hamid_Reza_Rashidi — Mon, 28 Dec 2020 11:35:53 GMT

First thanks for considering my case.

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

Re: Sort or rank a multi dimension table

Anil_Babu_Samineni — Mon, 28 Dec 2020 11:48:55 GMT

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

Re: Sort or rank a multi dimension table

Hamid_Reza_Rashidi — Wed, 30 Dec 2020 08:16:44 GMT

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.

Re: Sort or rank a multi dimension table

Anil_Babu_Samineni — Sat, 09 Jan 2021 15:22:58 GMT

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