topic Creating a score in the Script in QlikView

Creating a score in the Script

Anonymous — Wed, 15 Jul 2015 10:20:57 GMT

Hi all,

I'm new to Qlikview and I'm trying to create a score for both the unit and an average score for the buyer based on the value of a number of fields.

num(UNIT.M_M / 1000) + UNIT.AGE + (UNIT.FIX*10) + (UNIT.CC /1000) as Unit.GCT_Score,

num(avg(UNIT.M_M / 1000) + Avg(UNIT.AGE) + Avg(UNIT.FIX*10) + Avg(UNIT.CC /1000)) as Buyer.GCT_Score,

The first one (unit score) works brilliantly but when I load the script with the buyer score expression I get an error and it says I have an invalid expression in my script.

Does anyone have any ideas? Any help would be very much appreciated.

Thanks

Gareth

Re: Creating a score in the Script

morganaaron — Wed, 15 Jul 2015 10:26:34 GMT

Hi Gareth,

If you're using Avg() you're aggregating the data - are you using a Group By statement when loading? If not, you'll need to include the fields you want to group by for your aggregation in that statement.

Re: Creating a score in the Script

MayilVahanan — Wed, 15 Jul 2015 10:30:24 GMT

Whenever you use aggregation function in your load statement, you must use group by statement.

For Ex:

Load ID,

num(avg(UNIT.M_M / 1000) + Avg(UNIT.AGE) + Avg(UNIT.FIX*10) + Avg(UNIT.CC /1000)) as Buyer.GCT_Score

from datasource

group by ID;

Re: Creating a score in the Script

Anonymous — Wed, 15 Jul 2015 10:31:18 GMT

Hi Aaron,

Thanks for your response,

Like I said I am new, where would you add the Group by statement?

Re: Creating a score in the Script

Kushal_Chawda — Wed, 15 Jul 2015 10:32:29 GMT

You are missing group by clause. avg function is the aggregation function, so you need to include remaining fields in group by

Re: Creating a score in the Script

morganaaron — Wed, 15 Jul 2015 10:33:03 GMT

Underneath your source, so after loading you'd put:

From xxxxx.xxx

Group by Buyer;

If you just want an average for every buyer.

Re: Creating a score in the Script

Anonymous — Wed, 15 Jul 2015 11:18:54 GMT

It works!

Thank you everyone you have all been very helpful!

Gareth

Re: Creating a score in the Script

Anonymous — Wed, 15 Jul 2015 11:19:15 GMT

Thanks Aaron!

Re: Creating a score in the Script

Anonymous — Wed, 15 Jul 2015 12:28:50 GMT

Ok so like I said that worked brilliantly but now I want to add an if to the expression.

This is my script,

load

%_BNO,

if(UNIT.GRADEFIX = '' , '', num(avg(UNIT.M_MILEAGE / 1000) + Avg(UNIT.AGEMONTHS) + Avg(UNIT.GRADEFIX*10) + Avg(UNIT.CAPCLEAN_ORIG /1000))) as Buyer.GCT_Score

FROM

[$(QVD_Path)\BUYER_unit.qvd]

(qvd)

group by %_BNO;

It's again saying invalid expression?

Thanks

Gareth

Re: Creating a score in the Script

qlikviewwizard — Wed, 15 Jul 2015 13:43:08 GMT

Hi GarethCT

As your issue is resolved, please select Correct Answer.