Strange error: Invalid expression?

[#1]

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;

[alexandros17]

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

[#3]

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

R

[simenkg · Accepted solution]

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;

to

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

[#5]

Simen and all,

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

Ben