Link to home
Start Free TrialLog in
Avatar of Kongta
KongtaFlag for Switzerland

asked on

Percentage Ranking = Points

I would like to sort a list of data where all data respresents 100% and then would like to allot each data the full-percentage-point (uprounded) in a separate field like

Symbol          P1_1           [%]                               F1_1
abc                0.15            0.02%                          1
aed                0.16            0.04%                           1
....
edx                5.43            97.9%                           98
bde                6.15             100%                           100

I call the data by
SELECT     Symbol, P1_1
FROM         dbo.GFHilfstabelleV1
WHERE     (NOT (P1_1 IS NULL))

and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?

No idea if my thoughts can be placed in a code but appreciate any ideas
thx
Kongta
Avatar of mattclarified
mattclarified
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi,

All you need to do is put the formula =ROUNDUP(C1,0) into D1 for it to round up the points value for you

M@
Avatar of Kongta

ASKER

I do only have P1_1 and now looking for first to generate the percentage ranking and after to allot the points out of the percentage ranking and if possible, an option to avoid the outlier
How do you calculate the percentage?
Avatar of FemSteenkamp
do i understand the question correct:

you first want to calculate the % point column as follows ( P1_1 / Max (P1_1))
based on this result , remove lines from further calculation that have a
% < 0.5
and
% > 99.5   ( ie ignore the botom 0.5% vlaues and the top 0.5% values)

then from this reduced resultset, generate the F1_1 values.

so in you example the resultset will not contain abc,aed or the bde rows


Avatar of Kongta

ASKER

FemSteenkamp, the percentage relies on the ranking of each and not in relation to the MAX(P1_1) like if we have 100 data for example, then

(Rank)                      Symbol          P1_1           [%]                               F1_1
1                              abc                0.15            1%                                1
2                              aed                0.16            2%                                2
....
98                             edx                5.43            98%                           98
100                           bde                6.15             100%                           100

I would like to screen my 20'000 symbols on different data and at the end find the best third. I will after create an UPDATE/SET event therefore it has to contain abd,aed...
Avatar of Kongta

ASKER

mattclarified:How do you calculate the percentage?    no idea yet
SELECT     Symbol, P1_1,
cast (P1_1/max(P1_1) as numeric(18,6)) as[%],
cast(round ((P1_1/max(P1_1))* 100,0) as numeric(18,0)) as F1_1
FROM         dbo.GFHilfstabelleV1
WHERE     (NOT (P1_1 IS NULL))

I believe this is what you're after.

Hope it helps ;)
Dan
-- OK attempt two -- I reread all the above and here is a second atttempt... let me know !!

CREATE TABLE #Rank (
      Symbol nvarchar(10),
      P1_1 numeric (18,6),
    [%]  numeric (18,6),
    RankOrder int identity(1,1)
      )


Insert into #Rank (Symbol, P1_1, [%])
Select  Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
into #Rank
from dbo.GFHilfstabelleV1
WHERE     (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)

Select *
from #Rank
where [%] > 0.5 and [%] < 99.5
Avatar of Kongta

ASKER

get an error on 'into'

I now created a tblRank as I can't see the created table #Rank in my SQL, guess this is a tmpTbl? I'm not used to this, I ran once before but can't see, do I now have somewhere a ghost-tbl?
Avatar of Kongta

ASKER

I skipped the second 'into' on line 3, guess this is wrong, still get an error saying Symbol is not valid as it's not in an aggregate-function and not in a group-by?!
Avatar of Kongta

ASKER

Insert into tblRank (Symbol, P_1_1, [%])
SELECT     Symbol, P_1_1, CAST(P_1_1 / MAX(P_1_1) AS numeric(18, 6)) AS [%]
FROM         tblModellTmp
WHERE     (NOT (P_1_1 IS NULL))
GROUP BY Symbol, P_1_1
ORDER BY P_1_1 / MAX(P_1_1)

Select *
from tblRank
where [%] > 0.5 and [%] < 99.5

RESULT:

ZMD       -3.000000      1.000000      NULL
ZMH       14.517565      1.000000      NULL
ZMNO       5.294117      1.000000      NULL
ZMNO       6.049827      1.000000      NULL
ZQK       18.583334      1.000000      NULL
ZRAN       403.571442      1.000000      NULL
ZTR       5.186781      1.000000      NULL


changed dbo.GFHilfstabelleV1 into dbo.GFHilfstabelleV1 and P1_1 into P_1_1 as I used a more up-to-date.db. but there are no results
i thought you said the % calculation is not done by  P_1_1, CAST(P_1_1 / MAX(P_1_1) ???

so your comment
and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?

so not sure what you trying to do here then since now tou are trying code where you are calculating the % the way you said it is not done?

so try and explain what you are trying to do a bit better with more examples if you want more assistance
drop table tblRank
CREATE TABLE tblRank (
      Symbol nvarchar(10),
      P1_1 numeric (18,6),
    [%]  numeric (18,6),
    RankOrder int identity(1,1)
      )
-- Only run the table delete and Create statement above once


-- Yes #Rank is a temp table -- The Identity(1,1) is the key here - this will add the numbers to the field as the records are inserted into the temp table (#Rank) -- try this again -- oversite on my part
-- If you use your tabel tblRank -- add a field and add identity to it

Insert into tblRank (Symbol, P1_1, [%])
Select  Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
from dbo.GFHilfstabelleV1
WHERE     (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)

Select *
from tblRank
where [%] > 0.5 and [%] < 99.5
Avatar of Kongta

ASKER

okay, sorry for the confusion, I'm not English and is hard to explain:

I do have following ground data
Symbol      P_1_1
ZMH       14.517565    
ZMNO       5.294117    
ZMNO       6.049827      
ZQK       18.583334    
ZRAN       403.571442      
ZTR       5.186781    

the lowest value ist best. I now would like to order my 20'000 datas by P_1_1 and find out, at what percentage place each data lies. A workaround may by having a count-field, so first data gets '1', the worst '20000' and after go for 1/20000 = 0.00005 (=0.05%). Having this value, I would upround and save it in field F_1_1. Therefore, the best ones in class gets 1 and the worst ones in class gets 100. Doing this with these data on more values, at the end I get a ranking where I see which one is best on choosable values.

Let me know what I have to explain more, thx
Avatar of Kongta

ASKER

ran delete/create once, but can't follow on 'add a field and add identity to it ' as I can't choose 'identity', I made RankOrder as Key

after had following error
Meldung 8152, Ebene 16, Status 13, Zeile 3
Zeichenfolgen- oder Binärdaten was cut.

(0 Zeile(n) betroffen)
 
try the below
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0)
from (
	SELECT     Symbol, P1_1, row_number() over (order by P1_1) as rnk
	FROM         dbo.GFHilfstabelleV1
	WHERE     P1_1 is not null
) a

Open in new window

Avatar of Kongta

ASKER

nearly perfect ralmada, here a sample of the middle of the list. Where would you place the filter that the best 0,5% and the wort 0,5% are not part of the selection. I guess it's more honest to filter already at the basis, meaning that the 100% are represented by the 0,5% - 99,5% of P_1_1, not or does this make it more difficult?

PRM US      0      0.299682834
PRKR US      0      0.299760192
HPCL IN      2.06E-07      0.299837549
910 HK      0.009394905      0.299914907
CTDB US      0.0248      0.299992264
Something like this?
;with CTE as (
	select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
	from (
		SELECT     Symbol, P1_1, row_number() over (order by P1_1) as rnk
		FROM         dbo.GFHilfstabelleV1
		WHERE     P1_1 is not null
) 
select * from CTE
where perc between 0.05 and 0.995

Open in new window

The above will consider the .05 and .995 too, if you want to exclude them also, then do like this
;with CTE as (
	select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
	from (
		SELECT     Symbol, P1_1, row_number() over (order by P1_1) as rnk
		FROM         dbo.GFHilfstabelleV1
		WHERE     P1_1 is not null
) 
select * from CTE
where perc > 0.05 and perc < 0.995

Open in new window

Avatar of Kongta

ASKER

I get: 'Wrong syntax near Select' on line 8
missed one bracket
;with CTE as (
	select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
	from (
		SELECT     Symbol, P1_1, row_number() over (order by P1_1) as rnk
		FROM         dbo.GFHilfstabelleV1
		WHERE     P1_1 is not null
         ) 
)
select * from CTE
where perc > 0.05 and perc < 0.995

Open in new window

oh, well. Here is the correct one
;with CTE as (
	select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
	from (
		SELECT     Symbol, P1_1, row_number() over (order by P1_1) as rnk
		FROM         dbo.GFHilfstabelleV1
		WHERE     P1_1 is not null
         ) a
)
select * from CTE
where perc > 0.05 and perc < 0.995

Open in new window

Avatar of Kongta

ASKER

works, thx, one last hurdle: How to I place your code as a SET event?

UPDATE tblModellTmp
SET F_1_1 = perc  ??
                                          
                         ;with CTE as (
      select Symbol, P_1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
      from (
            SELECT     Symbol, P_1_1, row_number() over (order by P_1_1) as rnk
            FROM         tblModellTmp
            WHERE     P_1_1 is not null
         ) a
)
select * from CTE
where perc > 0.05 and perc < 0.995
Avatar of Kongta

ASKER

just learned about row_number() thx of you. can you explain me what the intention is with  'rnk * 1.0' wouldn't 'rnk / max(rnk)' be same?
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kongta

ASKER

Cool, have many thx for your great support, appreciate much
rgds
Kongta
Konqta or Ralmada can you show me the entire final code?

I can't figure out how to start TSQL using a "; with CTE"
Avatar of Kongta

ASKER

I copy/paste the code and it worked right away
maybe Ralmada can give a hint
I get it now -- thanks Ralmada  --  The "with CTE" is new to me... that will save me tons of coding.


You definitely proved why your rank is genius!



you're welcome.