4 Replies Latest reply: Jul 18, 2014 11:55 AM by Ben Paulson RSS

    Strange error: Invalid expression?

      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?
          Alessandro Saccone

          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?
            Rahul Lakhina

            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

            • Re: Strange error: Invalid expression?
              Simen Kind Gulbrandsen

              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