15 Replies Latest reply: Oct 9, 2015 3:01 PM by Sunny Talwar RSS

    Master Calendar script error

      The chart that I want to create a master calender for has a dimension of a field called 'Year Month'. I have following sections in my data load editor:

       

      [CRMReport_265409000001185003$]:

      LOAD OpportunitiesName,

      [Account Name],

      [Maga Lead on this Opp],

      [Closing Date],

      // [Year Month (Month) as Year Month],

      // [Month(Month) as Month],

      [Year],

      [1Yr Ceiling],

      [Amount],

      [Stage],

      [DESC:Stage],

      [Sector],

      [Mgmt Sector],

      [Functional Area]

      FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

      (biff, embedded labels, table is CRMReport_265409000001185003$);

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

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 

           

      Temp: 

      Load 

      min(Year Month) as minDate, 

      max(Year Month) as maxDate 

      Resident [CRMReport_265409000001185003$]; 

           

      Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

      DROP Table Temp; 

           

      TempCalendar: 

      LOAD 

      $(varMinDate) + Iterno()-1 As Num, 

      Date($(varMinDate) + IterNo() - 1) as TempDate 

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

      MasterCalendar: 

      Load 

        TempDate AS Year Month, 

        week(TempDate) As Week, 

        Year(TempDate) As Year, 

        Month(TempDate) As Month, 

        Day(TempDate) As Day, 

        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

        WeekDay(TempDate) as WeekDay 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar; 

       

       

       

      And I get the following error... What am I doing wrong?!

       

      The following error occurred:

      Error in expression: ')' expected

      The error occurred here:

      Temp: Load min(Year Month) as minDate, max(Year Month) as maxDate Resident [CRMReport_265409000001185003$]

        • Re: Master Calendar script error
          Sunny Talwar

          Try to put square brackets around Year Month field:

           

          Temp:

          Load

          min([Year Month]) as minDate,

          max([Year Month]) as maxDate

          Resident [CRMReport_265409000001185003$];

          • Re: Master Calendar script error
            Sunny Talwar

            May be this:

             

            [CRMReport_265409000001185003$]:

            LOAD OpportunitiesName,

            [Account Name],

            [Maga Lead on this Opp],

            [Closing Date],

            MonthName([Closing Date]) as YearMonth,

            // [Month(Month) as Month],

            [Year],

            [1Yr Ceiling],

            [Amount],

            [Stage],

            [DESC:Stage],

            [Sector],

            [Mgmt Sector],

            [Functional Area]

            FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

            (biff, embedded labels, table is CRMReport_265409000001185003$);

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

            QuartersMap:

            MAPPING

            LOAD Rowno() as Month,

                      'Q' & Ceil(RowNo()/3) as Quarter

            AUTOGENERATE (12);

               

            Temp:

            LOAD Min(YearMonth) as minDate,

                      Max(YearMonth) as maxDate

            Resident [CRMReport_265409000001185003$];

               

            Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

            Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

            DROP Table Temp;

               

            TempCalendar:

            LOAD $(varMinDate) + Iterno()-1 As Num,

                      Date($(varMinDate) + IterNo() - 1) as TempDate

            AutoGenerate 1

            While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

               

            MasterCalendar:

            LOAD MonthName(TempDate) as YearMonth,

                      Week(TempDate) As Week,

                      Year(TempDate) As Year,

                      Month(TempDate) As Month,

                      Day(TempDate) As Day,

                      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                      Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                      WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;

             

            or this:

             

            [CRMReport_265409000001185003$]:

            LOAD OpportunitiesName,

            [Account Name],

            [Maga Lead on this Opp],

            [Closing Date],

            MonthName([Closing Date]) as YearMonth,

            // [Month(Month) as Month],

            [Year],

            [1Yr Ceiling],

            [Amount],

            [Stage],

            [DESC:Stage],

            [Sector],

            [Mgmt Sector],

            [Functional Area]

            FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

            (biff, embedded labels, table is CRMReport_265409000001185003$);

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

            QuartersMap:

            MAPPING

            LOAD Rowno() as Month,

                      'Q' & Ceil(RowNo()/3) as Quarter

            AUTOGENERATE (12);

               

            Temp:

            LOAD Min([Closing Date]) as minDate,

                      Max([Closing Date]) as maxDate

            Resident [CRMReport_265409000001185003$];

               

            Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

            Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

            DROP Table Temp;

               

            TempCalendar:

            LOAD $(varMinDate) + Iterno()-1 As Num,

                      Date($(varMinDate) + IterNo() - 1) as TempDate

            AutoGenerate 1

            While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

               

            MasterCalendar:

            LOAD MonthName(TempDate) as [Closing Date],

                      Week(TempDate) As Week,

                      Year(TempDate) As Year,

                      Month(TempDate) As Month,

                      Day(TempDate) As Day,

                      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                      Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                      WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;

              • Re: Master Calendar script error

                Unfortunately, I still get the same error...

                 

                For the dimension, I have an expression below.

                 

                Date(     Date#( [Year Month],'YYYY-MM')      ,  'YYYY-MM'    ) 

                 

                Maybe something has to do with this expression?

                  • Re: Master Calendar script error
                    Sunny Talwar

                    Are you facing issues during refreshing the script, right?

                      • Re: Master Calendar script error

                        Yes, when I load the data after editing the script...

                          • Re: Master Calendar script error
                            Sunny Talwar

                            Can you share the error you are getting while reloading?

                              • Re: Master Calendar script error

                                Started loading data

                                QuartersMap << AUTOGENERATE(12) Lines fetched: 12

                                The following error occurred:

                                Table not found

                                 

                                The error occurred here:

                                Temp: Load

                                min([Month]) as minDate,

                                max([Month]) as maxDate

                                Resident [CRMReport_265409000001185003$]

                                Data has not been loaded. Please correct the error and try loading again.

                                  • Re: Master Calendar script error
                                    Sunny Talwar

                                    Can you copy paste the exact script and run it:

                                     

                                    CRMReport:

                                    LOAD OpportunitiesName,

                                    [Account Name],

                                    [Maga Lead on this Opp],

                                    [Closing Date],

                                    MonthName([Closing Date]) as YearMonth,

                                    // [Month(Month) as Month],

                                    [Year],

                                    [1Yr Ceiling],

                                    [Amount],

                                    [Stage],

                                    [DESC:Stage],

                                    [Sector],

                                    [Mgmt Sector],

                                    [Functional Area]

                                    FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

                                    (biff, embedded labels, table is CRMReport_265409000001185003$);

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

                                    QuartersMap:

                                    MAPPING

                                    LOAD Rowno() as Month,

                                              'Q' & Ceil(RowNo()/3) as Quarter

                                    AUTOGENERATE (12);

                                     

                                    Temp:

                                    LOAD Min(YearMonth) as minDate,

                                              Max(YearMonth) as maxDate

                                    Resident CRMReport;

                                     

                                    Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                                    Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                                    DROP Table Temp;

                                     

                                    TempCalendar:

                                    LOAD $(varMinDate) + Iterno()-1 As Num,

                                              Date($(varMinDate) + IterNo() - 1) as TempDate

                                    AutoGenerate 1

                                    While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                                     

                                    MasterCalendar:

                                    LOAD MonthName(TempDate) as YearMonth,

                                              Week(TempDate) As Week,

                                              Year(TempDate) As Year,

                                              Month(TempDate) As Month,

                                              Day(TempDate) As Day,

                                              ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                                              Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                                              WeekDay(TempDate) as WeekDay

                                    Resident TempCalendar

                                    Order By TempDate ASC;

                                    Drop Table TempCalendar;

                                    • Re: Master Calendar script error
                                      jagan mohan rao appala

                                      Hi Joung,

                                       

                                      Check whether you have this sheet CRMReport_265409000001185003$ in your excel file?  If yes then try to rename the Qv Table name like below.  I think $ in the table name causing the issue.

                                       

                                      [CRMReport]:

                                      LOAD OpportunitiesName,

                                      [Account Name],

                                      [Maga Lead on this Opp],

                                      [Closing Date],

                                      // [Year Month (Month) as Year Month],

                                      // [Month(Month) as Month],

                                      [Year],

                                      [1Yr Ceiling],

                                      [Amount],

                                      [Stage],

                                      [DESC:Stage],

                                      [Sector],

                                      [Mgmt Sector],

                                      [Functional Area]

                                      FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

                                      (biff, embedded labels, table is CRMReport_265409000001185003$);

                                        • Re: Master Calendar script error

                                          I still get the error below. Is there a reason why we uncomment  [Year Month (Month) as Year Month] and [Month(Month) as Month]?

                                           

                                          The following error occurred:

                                          Table not found

                                          The error occurred here:

                                          Temp: LOAD Min(Month) as minDate, Max(Month) as maxDate Resident [CRMReport]

                                          Data has not been loaded. Please correct the error and try loading again.

                                            • Re: Master Calendar script error
                                              Sunny Talwar

                                              I don't understand why you don't copy and paste the whole script. You error is showing that in your temp table you have Min(Month) as minDate, Max(Month) as maxDate....

                                               

                                              I am suggesting Temp table to be Min(YearMonth) as minDate, Max(YearMonth) as maxDate

                                               

                                              Can you please try and copy paste the complete script and run it to see if it works or not:

                                               

                                              CRMReport:

                                              LOAD OpportunitiesName,

                                              [Account Name],

                                              [Maga Lead on this Opp],

                                              [Closing Date],

                                              MonthName([Closing Date]) as YearMonth,

                                              // [Month(Month) as Month],

                                              [Year],

                                              [1Yr Ceiling],

                                              [Amount],

                                              [Stage],

                                              [DESC:Stage],

                                              [Sector],

                                              [Mgmt Sector],

                                              [Functional Area]

                                              FROM [lib://Desktop/Pipeline 05 29 2015 v.2.xls]

                                              (biff, embedded labels, table is CRMReport_265409000001185003$);

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

                                              QuartersMap:

                                              MAPPING

                                              LOAD Rowno() as Month,

                                                        'Q' & Ceil(RowNo()/3) as Quarter

                                              AUTOGENERATE (12);

                                               

                                              Temp:

                                              LOAD Min(YearMonth) as minDate,

                                                        Max(YearMonth) as maxDate

                                              Resident CRMReport;

                                               

                                              Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                                              Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                                              DROP Table Temp;

                                               

                                              TempCalendar:

                                              LOAD $(varMinDate) + Iterno()-1 As Num,

                                                        Date($(varMinDate) + IterNo() - 1) as TempDate

                                              AutoGenerate 1

                                              While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                                               

                                              MasterCalendar:

                                              LOAD MonthName(TempDate) as YearMonth,

                                                        Week(TempDate) As Week,

                                                        Year(TempDate) As Year,

                                                        Month(TempDate) As Month,

                                                        Day(TempDate) As Day,

                                                        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                                                        Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                                                        WeekDay(TempDate) as WeekDay

                                              Resident TempCalendar

                                              Order By TempDate ASC;

                                              Drop Table TempCalendar;