Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Creating a score in the Script

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

1 Solution

Accepted Solutions
morganaaron
Specialist
Specialist

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

From xxxxx.xxx

Group by Buyer;

If you just want an average for every buyer.

View solution in original post

9 Replies
morganaaron
Specialist
Specialist

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.

MayilVahanan

Hi

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Anonymous
Not applicable
Author

Hi Aaron,

Thanks for your response,

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

Kushal_Chawda

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

morganaaron
Specialist
Specialist

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

From xxxxx.xxx

Group by Buyer;

If you just want an average for every buyer.

Anonymous
Not applicable
Author

It works!

Thank you everyone you have all been very helpful!

Gareth

Anonymous
Not applicable
Author

Thanks Aaron!

Anonymous
Not applicable
Author

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

qlikviewwizard
Master II
Master II

Hi GarethCT

As your issue is resolved, please select Correct Answer.