Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have 3 variable and I need to calculate a score,
(weighting)--->
VAR1 30%
VAR2 30%
VAR3 30%
and the score must be <= 100 for example 90/100, 50/100 who can help me with ideas
thank you
Not really thinkable from this? Can you post little more content
Hello Anil
iin fact I need to calculate score by client
VAR1: MMM
VAR2 VRD
Var 3 senior in relationship
for example client x:
MMM: 500.0
VRD: 200.5
Senior 5 years
Score:
load
CLIENT_ID,
MMM,
VRD,
RELATION,
MMM/(MAX (MMM))*100 as MMM1,
VRD/(MAX (VRD))*100 AS VRD1,
RELATION/(MAX (RELATION))*100 AS RELATION1
from
Score1:
load
CLIENT_ID,
MMM,
VRD,
RELATION+MMM1+VRD1 AS iNITIAL_Score
resident Score;
DROP TABLE Score;
Score_final:
load
CLIENT_ID,
MMM,
VRD,
(INITIAL_Score)/MAX(INITIAL_Score)*100 AS FINAL_Score
resident Scor
DROP
TABLE Score1;
Perhaps this?
Score:
load CLIENT_ID, MMM, VRD, RELATION
from
Right Join (Score)
Load *, RangeSum(MMM1,VRD1,RELATION1) as INITITAL_Score;
Load CLIENT_ID, MMM, VRD, RELATION
MMM/(MAX (MMM))*100 as MMM1,
VRD/(MAX (VRD))*100 AS VRD1,
RELATION/(MAX (RELATION))*100 AS RELATION1
Resident Score Group By CLIENT_ID, MMM, VRD, RELATION;
Final:
NoConcatenate
Load * Resident Score;
Right Join(Score)
Final:
Load CLIENT_ID, MMM, VRD,
INITIAL_Score/Max(INITIAL_Score)*100 as FINAL_Score
Resident Score Group By CLIENT_ID, MMM, VRD;
Drop Table Sample;
Note:
1) Group By Won't work while load table itself
2) To get that you need Resident from first table to get Max value from fields on behalf of table
3) The same i copied that table into new table
4) Finally, I did same again like 2nd point.
Anil why i put all column in group by
Resident Score Group By CLIENT_ID, MMM, VRD;
i need just put clientID
Because, Group By should need to use all non aggregate fields..
So , in my case what is the solution ?
Can u please provide sample
i tried this script
CLIENT1:
LOAD %ID_CLIENT as ID_CLIENT,
%ID_AGENCE_CTOS_CLIENT,
%ID_COMPTE,
[1]
FROM
(qvd);
inner join
LOAD ID as %ID_COMPTE,
MMM,
SMV,
NBR
FROM
(qvd);
CLIENT2:
load
%ID_COMPTE,
// MMM,
//
// SMV,
//
// NBR,
// ID_CLIENT,
%ID_AGENCE_CTOS_CLIENT
resident CLIENT1;
Join(CLIENT1)
LOAD
%ID_AGENCE_CTOS_CLIENT,
MAX (MMM) as MMM1,
MAX (SMV) AS SMV1,
MAX (NBR) AS NBR1
Resident CLIENT1
Group BY %ID_AGENCE_CTOS_CLIENT
;
DROP Table CLIENT1;
Score:
LOAD CLIENT_ID,
MMM,
SMV,
NBR,
((MMM / MMM1) * 100*30 +(SMV / SMV1) * 100 *30+ (SMV / SMV1) * 100*300)/100 as test
Resident CLIENT2;
//
//
//
DROP Table CLIENT2;
But
MAX (MMM) as MMM1,
MAX (SMV) AS SMV1,
MAX (NBR) AS NBR1 all null