3 Replies Latest reply: Mar 23, 2017 7:50 AM by Sumit panda RSS

    Error in expression

    Pedro Gentile

      Hi everyone,

       

      I got stuck in a Master Calendar creation and have been for days trying to fix it unsuccessfully. Please, some one could give a hand to go through it?

       

      I guess the error is in the inyeartodate statement.  But couldnt fix it.

       

       

      MinMax:

      LOAD

      Min(OrderDate) as MinDate,

      Max(OrderDate) as MaxDate

       

       

      Resident Orders;

       

       

      LET vMindDate = Num(Peek('MinDate', 0, 'MinxMax'));

      LET vMaxDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vToday = $(VMaxDate);

       

       

      //***************************** Temporary Calendar *****************************

      TempCal:

      LOAD

       

       

      date($(vMindDate) + rowno() - 1) AS TempDate

      AutoGenerate  $(vMaxDate) - $(vMindDate) +1;

       

        DROP TABLE MinMax;

       

        //***************************** Master Calendar *****************************

      MasterCalendar:

      LOAD

        TempDate as OrderDate,

        Week(TempDate) as Week,

      year(TempDate) as Year,

      Month(TempDate) AS Month,

      Day(tempDate) AS Day,

      Weekday(TempDate) AS WeekDay,

      'Q' & ceil(month(TempDate) / 3) AS Quarter,

      Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,

      Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

      inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

      InYearToDate(tempDate, $(vToday), -1)* -1 AS LastYTDFlag

      Resident TempCal

      ORDER By TempDate ASC;

      DROP TABLE TempCal;

       

       

      ERROR:

      Error in expression:

      ')' expected

      MasterCalendar:

      LOAD

        TempDate as OrderDate,

        Week(TempDate) as Week,

      year(TempDate) as Year,

      Month(TempDate) AS Month,

      Day(tempDate) AS Day,

      Weekday(TempDate) AS WeekDay,

      'Q' & ceil(month(TempDate) / 3) AS Quarter,

      Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,

      Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

      inyeartodate(TempDate, , 0) * -1 AS CurYTDFlag,

      InYearToDate(tempDate, , -1)* -1 AS LastYTDFlag

      Resident TempCal

      ORDER By TempDate ASC;

        • Re: Error in expression
          Andrey Khoronenko

          Hi Pedro,

           

          Look here (attached file, page 5).

           

          Regards,

          Andrey

          • Re: Error in expression
            Sunny Talwar

            Your vToday is incorrectly defined

             

            LET vToday = $(VMaxDate); -> Should be vMaxDate

             

            But, why do you need vToday? Use vMaxDate instead:

             

            MasterCalendar:

            LOAD

              TempDate as OrderDate,

              Week(TempDate) as Week,

            year(TempDate) as Year,

            Month(TempDate) AS Month,

            Day(tempDate) AS Day,

            Weekday(TempDate) AS WeekDay,

            'Q' & ceil(month(TempDate) / 3) AS Quarter,

            Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,

            Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

            inyeartodate(TempDate, $(vMaxDate), 0) * -1 AS CurYTDFlag,

            InYearToDate(tempDate, $(vMaxDate), -1)* -1 AS LastYTDFlag

            Resident TempCal

            ORDER By TempDate ASC;

            DROP TABLE TempCal;

            • Re: Error in expression
              Sumit panda

              HI Pedro,

              There are couple of typo in the section against tempDate.

              In the Day function the field is tempDate, so in the third to last row.

               

              Please refrain from using variables in the load statement as they are directly replaced with values which sometimes create errors.

              Your vToday is incorrectly defined as well.

              PFB the statement i created.

               

              MasterCalendar:

              LOAD

              TempDate as OrderDate,

              Week(TempDate) as Week,

              year(TempDate) as Year,

              Month(TempDate) AS Month,

              DayName(TempDate) AS Day,

              WeekDay(TempDate) AS WeekDay,

              'Q' & ceil(month(TempDate)/3) AS Quarter,

              Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthYear,

              Week(weekstart(TempDate))&'-'& Year(TempDate) AS WeekYear,

              YearToDate(TempDate) * -1 AS CurYTDFlag,

              //inYearToDate(TempDate,$(vToday), 0) * -1 AS CurYTDFlag,

              YearToDate(TempDate,-1)* -1 AS LastYTDFlag

              //inYearToDate(TempDate, $(vToday), -1)* -1 AS LastYTDFlag

              Resident TempCal ORDER By TempDate ASC;

               

               

              DROP TABLE TempCal;

               

              Thanks and regards,

              Sumit