Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Underneath your source, so after loading you'd put:
From xxxxx.xxx
Group by Buyer;
If you just want an average for every buyer.
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.
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;
Hi Aaron,
Thanks for your response,
Like I said I am new, where would you add the Group by statement?
You are missing group by clause. avg function is the aggregation function, so you need to include remaining fields in group by
Underneath your source, so after loading you'd put:
From xxxxx.xxx
Group by Buyer;
If you just want an average for every buyer.
It works!
Thank you everyone you have all been very helpful!
Gareth
Thanks Aaron!
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
Hi GarethCT
As your issue is resolved, please select Correct Answer.