Master Calendar script error

[#1]

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$]

[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$];

[#3]

I still get the same error.

[sunny_talwar]

Another thing I see is that the Year Month is commented out in your [CRMReport_265409000001185003$].

If you plan to use a field in a resident load, you need to make sure it exists in your resident table.

May be something like this:

MonthName([Closing Date]) as [Year Month]

[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;

[#6]

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?

[sunny_talwar]

Are you facing issues during refreshing the script, right?

[#8]

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

[sunny_talwar]

Can you share the error you are getting while reloading?

[#10]

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.