Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

souadouert
Contributor III

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

11 Replies

Re: Scoring system

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

souadouert
Contributor III

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

souadouert
Contributor III

Re: Scoring system

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;

Re: Scoring system

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
Contributor III

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..

souadouert
Contributor III

Re: Scoring system

So , in my case what is the solution ?

Re: Scoring system

Can u please provide sample

souadouert
Contributor III

Re: Scoring system

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

Community Browser