Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

garethct
Contributor

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

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.

9 Replies
morganaaron
Valued Contributor

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.

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;

garethct
Contributor

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?

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

morganaaron
Valued Contributor

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.

garethct
Contributor

Re: Creating a score in the Script

It works!

Thank you everyone you have all been very helpful!

Gareth

garethct
Contributor

Re: Creating a score in the Script

Thanks Aaron!

garethct
Contributor

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

Arjunarao
Honored Contributor II

Re: Creating a score in the Script

Hi GarethCT

As your issue is resolved, please select Correct Answer.

Community Browser