Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
'*' 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.
Thank you, now it works.
there was a field not in the group by
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.