11 Replies Latest reply: Jan 9, 2018 8:46 PM by Anil Samineni

Scoring system

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

• Re: Scoring system

Not really thinkable from this? Can you post little more content

• Re: Scoring system

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

• Re: Scoring system

Score:

CLIENT_ID,

MMM,

VRD,

RELATION,

MMM/(MAX (MMM))*100 as MMM1,

VRD/(MAX (VRD))*100 AS VRD1,

RELATION/(MAX (RELATION))*100 AS RELATION1

from

[C:\Users\90160\Documents\document\CrditConta\*17.xls];

Score1:

CLIENT_ID,

MMM,

VRD,

RELATION+MMM1+VRD1 AS iNITIAL_Score

resident Score;

DROP TABLE Score;

Score_final:

CLIENT_ID,

MMM,

VRD,

(INITIAL_Score)/MAX(INITIAL_Score)*100 AS FINAL_Score

resident Scor

DROP

TABLE Score1;

• Re: Scoring system

Perhaps this?

Score:

from

[C:\Users\90160\Documents\document\CrditConta\*17.xls];

Right Join (Score)

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

Right Join(Score)

Final:

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.

• Re: Scoring system

Anil why i put all column in group by

Resident Score Group By CLIENT_ID, MMM, VRD;
i need just put clientID

• Re: Scoring system

Because, Group By should need to use all non aggregate fields..

• Re: Scoring system

So , in my case what is the solution ?

• Re: Scoring system

i tried this script

CLIENT1:

%ID_AGENCE_CTOS_CLIENT,

%ID_COMPTE,

[1]

FROM

[C:\Users\90160\Desktop\portfeuille\client.qvd]

(qvd);

inner join

MMM,

SMV,

NBR

FROM

[C:\Users\90160\Desktop\portfeuille\MMM.qvd]

(qvd);

CLIENT2:

%ID_COMPTE,

//     MMM,

//

//     SMV,

//

//     NBR,

//     ID_CLIENT,

%ID_AGENCE_CTOS_CLIENT

resident CLIENT1;

Join(CLIENT1)

%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:

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

• Re: Scoring system

Can you share Client and MMM QVD's to check?

• Re: Scoring system

Do you missed any attachment or delete by chance?