Invalid expression problem

[#1]

Hi. I'm trying to calculate the CC returns from my script. This is the part of the script:

AZIONI_3:

NoConcatenate load

*,

if (Azione=peek(Azione,-1), peek([Adj Close],-1),'-') as Val_close_prec

resident AZIONI_2;

drop table AZIONI_2;

CC:

NoConcatenate load

*,

-log((Avg(Val_close_prec)))+log(Avg([Adj Close])) as CC

Resident AZIONI_3;

When i run it it shows this error:

Invalid expression

CC:

NoConcatenate LOAD

*,

-log((Avg(Val_close_prec)))+log(Avg([Adj Close])) as CC

Resident AZIONI_3

I don't understand where the problem is! When i use that formula as expression in a pivot table it works...

Can somebody explain the error to me?

[jpapador]

Well for one if you are using avg you will need a group by in your load statement

EX

Resident AZIONI_3

Group by (all the fields included in the *)

[#3]

I tried! There is always the same error!

[jpapador]

Are you sure you can do a -log in the script.  Is it possible to do

log(Avg(Val_close_prec))*-1 + log(Avg([Adj Close]))

Also you have 2 parenthesis after the first log and not the second... That might be intended tho

[anbu1984]

Can you post the script you tried.

Run this script as is. You will get invalid expr error and also run it with Group by after removing ; and comment

Load Prod,Log(Avg(Sales)); // Group By Prod;

Load * Inline [

Prod,Year,Sales

p1,2014,100

p1,2015,50

p2,2014,1000

p2,2015,500 ];

[jonathandienst · Accepted solution]

Hi

If you use any aggregation functions (Avg() in your case) in a LOAD statement, all other fields need to be listed in a Group By statement:

CC:

NoConcatenate load

*,

-log((Avg(Val_close_prec)))+log(Avg([Adj Close])) as CC

Resident AZIONI_3

Group By

     a,

     b,

     ...

;

All the fields returned by the * must be explicitly stated under the Group By clause. (a, b, ... should be replaced by the fields in AZIONI_3).

HTH

Jonathan

[#7]

yes you were right.. I forgot to add the key in the group by.. Now it works! Thanks to all!