Discussion Board for collaboration on QlikView Scripting.
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.
Go to Solution.
Underneath your source, so after loading you'd put:
Group by Buyer;
If you just want an average for every buyer.
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.
Whenever you use aggregation function in your load statement, you must use group by statement.
num(avg(UNIT.M_M / 1000) + Avg(UNIT.AGE) + Avg(UNIT.FIX*10) + Avg(UNIT.CC /1000)) as Buyer.GCT_Score
group by ID;
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
Thank you everyone you have all been very helpful!
Ok so like I said that worked brilliantly but now I want to add an if to the expression.
This is my script,
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
group by %_BNO;
It's again saying invalid expression?
As your issue is resolved, please select Correct Answer.