topic Re: Invalid expression problem in QlikView

Invalid expression problem

Mon, 12 Jan 2015 21:55:20 GMT

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?

Re: Invalid expression problem

jpapador — Mon, 12 Jan 2015 22:04:25 GMT

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

Resident AZIONI_3

Group by (all the fields included in the *)

Re: Invalid expression problem

Mon, 12 Jan 2015 22:12:38 GMT

I tried! There is always the same error!

Re: Invalid expression problem

jpapador — Mon, 12 Jan 2015 22:20:55 GMT

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

Re: Invalid expression problem

anbu1984 — Tue, 13 Jan 2015 06:06:10 GMT

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 ];

Re: Invalid expression problem

jonathandienst — Tue, 13 Jan 2015 06:16:44 GMT

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

...

;

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

Re: Invalid expression problem

Tue, 13 Jan 2015 09:12:16 GMT

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