Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to create a system where partners are scored based on what quartile they are in, in regards to GP%.
For example the 4th quartile of GP% would get 20 points, 3rd 15 points, 2nd 10 points for example.
So if JD sports' average GP% is in the 4th quartile, JD sports will get 20 points.
Thank you.
Hello,
There is certainly a way to optimize it but here is a first solution to your need
Test:
Load *
Inline [
Dim,GP
JD,2
JD,4
JD,8
AB,3
AB,2
AB,6
CE,1
CE,4
CE,30
PO,3
PO,9
PO,7
];
left join(Test)
TEST2:
Load
1 as TEMP,
Dim,
avg(GP) as AVG_GP
Resident Test
group by Dim;
NoConcatenate
TEST3:
LOad
TEMP,
Fractile(AVG_GP,0.25) as Fractile_25,
Fractile(AVG_GP,0.50) as Fractile_50,
Fractile(AVG_GP,0.75) as Fractile_75
Resident Test
group by TEMP
;
let vFractile_25 = peek('Fractile_25',-1,'TEST3');
let vFractile_50 = peek('Fractile_50',-1,'TEST3');
let vFractile_75 = peek('Fractile_75',-1,'TEST3');
NoConcatenate
Final:
Load
Dim,
GP,
AVG_GP,
if(AVG_GP <= $(vFractile_25), 20,
if(AVG_GP <= $(vFractile_50), 15,
if(AVG_GP <= $(vFractile_75), 10,
5))) as Fractile
Resident Test;
Drop Tables Test,TEST3;
Hello,
There is certainly a way to optimize it but here is a first solution to your need
Test:
Load *
Inline [
Dim,GP
JD,2
JD,4
JD,8
AB,3
AB,2
AB,6
CE,1
CE,4
CE,30
PO,3
PO,9
PO,7
];
left join(Test)
TEST2:
Load
1 as TEMP,
Dim,
avg(GP) as AVG_GP
Resident Test
group by Dim;
NoConcatenate
TEST3:
LOad
TEMP,
Fractile(AVG_GP,0.25) as Fractile_25,
Fractile(AVG_GP,0.50) as Fractile_50,
Fractile(AVG_GP,0.75) as Fractile_75
Resident Test
group by TEMP
;
let vFractile_25 = peek('Fractile_25',-1,'TEST3');
let vFractile_50 = peek('Fractile_50',-1,'TEST3');
let vFractile_75 = peek('Fractile_75',-1,'TEST3');
NoConcatenate
Final:
Load
Dim,
GP,
AVG_GP,
if(AVG_GP <= $(vFractile_25), 20,
if(AVG_GP <= $(vFractile_50), 15,
if(AVG_GP <= $(vFractile_75), 10,
5))) as Fractile
Resident Test;
Drop Tables Test,TEST3;