Scoring system

[souadouert]

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

[Anil_Babu_Samineni]

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

[souadouert]

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

[souadouert]

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;

[Anil_Babu_Samineni]

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.

[souadouert]

Anil why i put all column in group by

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

[Anil_Babu_Samineni]

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

[souadouert]

So , in my case what is the solution ?

[Anil_Babu_Samineni]

Can u please provide sample

[souadouert]

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