topic Re: Invalid expression in QlikView

Invalid expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi, i am new to qlikview and I've a problem..

I have to calculate CC (continuous compounding) in the script,

I use this expression:

DatiTmp:

NoConcatenate LOAD *, if(Azione = Peek(Azione, -1), Peek([Adj Close], -1), '-') AS PreviousClose, Date(MonthStart(Date), 'YYYY-MMM') AS YearMonth Resident Dati;

DROP Table Dati;

DatiCC:

LOAD *, (log(Avg(PreviousClose)) * (-1) + log(Avg([Adj Close]))) AS CC Resident DatiTmp

Group By Year, Month, Azione, Date, Open, High, Low, Close, Volume, [Adj Close], PreviousClose, YearMonth

but when I try to reload the script something is wrong and appear a script error (Invalid Expression)

What could be the problem?

I'm sure that the expression is correct.

Thanks in advance

Re: Invalid expression

rubenmarin — Sat, 12 Mar 2016 15:46:59 GMT

Hi Federica, maybe is becuse of the group by, that clause must contain every field that is not in an aggregation function. I'm not sure of this because you load fields with '*' and I don't know how many field has that table, to make a test you can try loading only the fields in the Group By, this way you can test is the error was in there:

DatiCC:

LOAD Year, Month, Azione, Date, Open, High, Low, Close, Volume, [Adj Close], PreviousClose, YearMonth,

(log(Avg(PreviousClose)) * (-1) + log(Avg([Adj Close]))) AS CC Resident DatiTmp

Group By Year, Month, Azione, Date, Open, High, Low, Close, Volume, [Adj Close], PreviousClose, YearMonth

Re: Invalid expression

Digvijay_Singh — Sun, 13 Mar 2016 04:22:44 GMT

'*' is causing error it seems, You are grouping your transactions based on Group By fields so any higher granular field if mentioned after load which is not there in group by will contradict the meaning of grouping logic.

Not sure but fields [Adj Close], PreviousClose if mentioned in Group By then the newly created field 'CC' will appear in front of every value of Adj CLose and Previous Close. So the avg function won't work as desired AFAIK.

Re: Invalid expression

Sun, 13 Mar 2016 09:28:20 GMT

Thank you, now it works.

there was a field not in the group by

Re: Invalid expression

Kushal_Chawda — Sun, 13 Mar 2016 09:39:49 GMT

1) Try to check all the fields in Group By clause, may be you are missing some field in Group By Clause which is available in that load statement. Also use relevant aggregation function on Measures instead of including in Group by clause for eg. Previous Close, Close Volume

2) Instead of using * , list out the field names(in load statement) available in Group By Clause.