topic Re: Strange error: Invalid expression? in QlikView

Strange error: Invalid expression?

Fri, 18 Jul 2014 13:10:13 GMT

Dear Community,

While coding an if flag in my calendar script, I got an invalid expression error. I know that the expression Num(Month(Today()))=Max(Month(AccountValue.AsOfDate)) must have something to do with it, because the other expression ((Num(Month(Date))=12) runs fine on its own in a text box. The purpose of the if flag is to give the maximum date (the AccountValue.AsOfDate) that the company has so far tracked its accounts this year--the rough equivalent is the last month--and, for all years previous, give the end of year date in December. However, even though the syntax isn't flagged, I'm getting this error. Could someone explain it to me? Thanks!

Ben

--------------------------------------

Invalid expression

MAPPING LOAD

Num(CalendarExclusions.Date) as CalendarExclusions.Date,

CalendarExclusions.WorkingDayCounter

FROM C:\QlikView\Development\QVD\Historical Analysis\SalesQVDLoad\CalendarExclusions.qvd (qvd);

Set vDateSourceTable = 'LinkTable';

Let vToday = num(Today());

TempCalendar:

Load Distinct

Date(Daystart(Key|Date),'M/DD/YYYY') AS Date

Resident $(vDateSourceTable);

TempAccountValue:

left join (TempCalendar)

Load Distinct

AccountValue.AsOfDate

Resident AccountValue;

Calendar:
LOAD Distinct
Date(Daystart(Date),'M/DD/YYYY') AS Key|Date,
Date,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),0) as WorkingDayFlag,

Year(Date) AS Year,
Year(Date) - 1 AS LYear,
Month(Date) AS Month,
Num(Month(Date)) AS MonthNumber,

If(Year(Today())=Year(Date) And Num(Month(Today()))=Max(Month(AccountValue.AsOfDate)),1,
If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,
IF(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null()) as FilteredYear,
Week(Date) as WeekNumber,
Text(Date(monthstart(Date), 'MMM-YYYY')) AS MonthYear,
IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),Null()) * week(Date) as Week,

If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) AS Quarter,
If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) & '-' & Year(Date) AS QuarterYear,
Ceil(Day(Date)/7,1) as MonthWeekNumber

TempCalendar;
Drop Table TempCalendar;
Drop Table TempAccountValue;

Re: Strange error: Invalid expression?

alexandros17 — Fri, 18 Jul 2014 13:14:08 GMT

You are using an aggregation function (Max) without a group by clause so the load cannot compute the max of the expression.

Try to load without max (only to see if that is the problem), if you have not errors you need to compute max in another table and then left join your main table with the one with max

Hope it helps

Re: Strange error: Invalid expression?

Fri, 18 Jul 2014 13:19:08 GMT

Dear Ben,

I believe this is because Max() is an aggregation function and would give you an invalid expression error in the absence of a group by clause.

If i was you i would load that field separately and left join into the Calendar table.

Regards

Re: Strange error: Invalid expression?

simenkg — Fri, 18 Jul 2014 13:21:32 GMT

Hi, Ben.

As Alessandro States, you cannot use an aggregation function without a group by statement. However, the solution is not to add a group by statement.

You can fix this error by changing

TempAccountValue:

left join (TempCalendar)

Load Distinct

AccountValue.AsOfDate

Resident AccountValue;

TempAccountValue:

Outer join (TempCalendar)

Load

max(AccountValue.AsOfDate) as MaxAsOfDate

Resident AccountValue;

Then you can change your if to:

f(Year(Today())=Year(Date) And Num(Month(Today()))=Month(MaxAsOfDate),1,

If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,

Hope this helps

Regards
SKG

Re: Strange error: Invalid expression?

Fri, 18 Jul 2014 15:55:08 GMT

Simen and all,

Thanks! It's progressing past that point in the load. I really appreciate the quick response.

Ben