Master Calendar issue

[#1]

Hi Guys,

I am stuck in defining the Min and Max date (last load statement) while making the master calendar. Someone an idea?

My Excel  files shows dates as follows: "December 2016 Final" (this is one cell)

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

RepMonth:

Load Date(Date#(SubField("[$C].[Reporting Date]",' ',1),'MMMM'),'MMM') as Month,
SubField("[$C].[Reporting Date]",' ',2) as Year

from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);


MasterCalendar:

Load Date("[$C].[Reporting Date]") AS [Reporting Month],
Year("[$C].[Reporting Date]") AS Year,
'Q'& Ceil (Month(("[$C].[Reporting Date]")) / 3) AS Quarter,
Month("[$C].[Reporting Date]") As Month,
Day("[$C].[Reporting Date]") As Day,
Week("[$C].[Reporting Date]") As Week;

Load
Date(MinDate + IterNo() -1 ) As "[$C].[Reporting Date]"
While (MinDate + IterNO() -1) <= Num(MaxDate);

Load
Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,
Max(date(Date#("[$C].[Reporting Date"))) as MaxDate
RESIDENT RepMonth ;

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

[micheledenardi]

In Your last load statement you had forgot some ) and ]

Load
Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,
Max(date(Date#("[$C].[Reporting Date]"))) as MaxDate
RESIDENT RepMonth ;

[its_anandrjs]

Try your last code this way

Load

Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,

Max(date(Date#("[$C].[Reporting Date]"))) as MaxDate

RESIDENT RepMonth ;

Regards

Anand

[Anonymous]

Check Closing parenthesis " )" missing in last load statement. Give and try once

[#5]

Thanks Guys, still not working out though. I get a table not found errror

[Anonymous]

Attach sample file

[prieper]

Check your spelling. Seems that formula refers to a name, which does not exist.

[#8]

Can't upload file unfortunately as I am @ work.

But it's quite simple. The script above is the only script I am using and its linking directly with the excel file. The name matches as presented in the Excel and I am only getting an error at the last load statement.

Script is a direct copy (where I amended the necessary fields). It could be that there are codes that shouldn't be there.

Most important is that dates in Excel are displayed as "December 2016 Final hence the load statement @ top

[prieper]

The Table RepMonth does not have the field "[$C].[Reporting Date]" included, you load only Year and Month.

[#10]

Still getting following error:

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

Field not found - <[$C].[Reporting Date]>

Load

Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,

Max(date(Date#("[$C].[Reporting Date"))) as MaxDate

RESIDENT RepMonth

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