Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Invalid expression problem

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?

1 Solution

Accepted Solutions
jonathandienst

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
jpapador
Partner
Partner

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 *)

Not applicable

I tried! There is always the same error!

jpapador
Partner
Partner

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
Master III
Master III

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Not applicable

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