Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Invalid expression problem

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
6 Replies
jpapador
Valued Contributor

Re: Invalid expression problem

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

Re: Invalid expression problem

I tried! There is always the same error!

jpapador
Valued Contributor

Re: Invalid expression problem

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
Honored Contributor III

Re: Invalid expression problem

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

MVP
MVP

Re: Invalid expression problem

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
Not applicable

Re: Invalid expression problem

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

Community Browser