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]

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;

[jagan]

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$);

[#4]

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.

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

[#6]

Im sorry, this is what I meant to put

The following error occurred:

Table not found

The error occurred here:

Temp: LOAD Min(YearMonth) as minDate, Max(YearMonth) as maxDate Resident CRMReport

[sunny_talwar]

Can you share your application to look at?