Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Specialist
Specialist

Re: Creating a score in the Script

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
Highlighted
Specialist
Specialist

Re: Creating a score in the Script

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.

Highlighted

Re: Creating a score in the Script

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;

Highlighted
Creator
Creator

Re: Creating a score in the Script

Hi Aaron,

Thanks for your response,

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

Highlighted

Re: Creating a score in the Script

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

Highlighted
Specialist
Specialist

Re: Creating a score in the Script

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

Highlighted
Creator
Creator

Re: Creating a score in the Script

It works!

Thank you everyone you have all been very helpful!

Gareth

Highlighted
Creator
Creator

Re: Creating a score in the Script

Thanks Aaron!

Highlighted
Creator
Creator

Re: Creating a score in the Script

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

Highlighted
Master II
Master II

Re: Creating a score in the Script

Hi GarethCT

As your issue is resolved, please select Correct Answer.