topic Re: Scoring system in QlikView

Scoring system

souadouert — Tue, 09 Jan 2018 08:32:29 GMT

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

Anil_Babu_Samineni — Tue, 09 Jan 2018 08:56:24 GMT

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

Re: Scoring system

souadouert — Tue, 09 Jan 2018 09:02:43 GMT

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

souadouert — Tue, 09 Jan 2018 09:18:11 GMT

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

Anil_Babu_Samineni — Tue, 09 Jan 2018 09:35:46 GMT

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.

Re: Scoring system

souadouert — Tue, 09 Jan 2018 10:50:11 GMT

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

Anil_Babu_Samineni — Tue, 09 Jan 2018 11:40:54 GMT

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

Re: Scoring system

souadouert — Tue, 09 Jan 2018 12:20:00 GMT

So , in my case what is the solution ?

Re: Scoring system

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:28:22 GMT

Can u please provide sample

Re: Scoring system

souadouert — Tue, 09 Jan 2018 14:18:23 GMT

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

Re: Scoring system

Anil_Babu_Samineni — Tue, 09 Jan 2018 14:21:41 GMT

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

Re: Scoring system

Anil_Babu_Samineni — Wed, 10 Jan 2018 01:46:25 GMT

Do you missed any attachment or delete by chance?