Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I receive the following error:-
Field not found - <Year>
MasterCalendar:
when loading the following script?:-
MasterCalendar:
Load
TempDate AS %Date,
Week(TempDate),'yyyyMMDD' As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day, WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth // Numeric fiscal month
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Any help appreciated.
Laura
Another way is using preceding load as below:
MasterCalendar:
Load *,
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth; // Numeric fiscal month
Load
TempDate AS %Date,
Week(TempDate),'yyyyMMDD' As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day, WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I hope this is helpful.
Replace Year with Year(TempDate) and month with Month(TempDate) in fyear field.
Year(TempDate) + If(Month(TempDate)>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month(TempDate)-$(vFM), 12)+1 as fMonth // Numeric fiscal month
After made this changes reload it again.
MasterCalendar:
Load
TempDate AS %Date,
Week(TempDate),'yyyyMMDD' As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day, WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth // Numeric fiscal month
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Above underlined was ur issue in script, bcoz you are creating those fileds in the same table only it will not work.
If u want those take again resident of it create ur FYear and FMonth
-Nagarjun
Another way is using preceding load as below:
MasterCalendar:
Load *,
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth; // Numeric fiscal month
Load
TempDate AS %Date,
Week(TempDate),'yyyyMMDD' As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day, WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay,
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I hope this is helpful.
you may have error again from Month
Hi
Try like this
MasterCalendar:
LOad *,
Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth; // Numeric fiscal month
Load
TempDate AS %Date,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
WeekStart(TempDate) as WeekStart,
WeekEnd(TempDate) as WeekEnd,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'),'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;