Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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
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
Simen and all,
Thanks! It's progressing past that point in the load. I really appreciate the quick response.
Ben