I'm encountering an issue mapping a fiscal year to my master calendar. I'm trying to use an inline mapping load for simplicity, also because I don't know a better way. I need to map fiscal year and fiscal quarter to my master calendar. for fiscal calendar my map is:
FiscalQuarter_Map:
MAPPING LOAD * INLINE [
Month, FiscalQuarter
1, Q3
2, Q3
3, Q3
4, Q4... and so on ];
with applymap('FiscalQuarter_Map', Month(TempDate)) as FiscalQuarter in my MasterCalendar load script. It works nicely.
FiscalYear turned out to be a bit more complicated because I tried to map it using a field I created, MonthYear. In the MasterCalendar load script MonthYear is defined as: Date(monthstart(TempDate), 'MMM-YY') AS MonthYear So my map is:
FiscalYear_Map:
MAPPING LOAD * INLINE [
MonthYear, FiscalYear
Aug-06, 2007
Sep-06, 2007
etc etc etc
Jul-10, 2010 ];
I tried applymap('FiscalYear_Map', Date(monthstart(TempDate), 'MMM-YY')) as FiscalYear ... However, then the list box FiscalYear returns the same thing as my MonthYear field instead of 2007 2008 2009 2010 which I would expect. So it is returning Aug-06 Sep-06 etc. If I try 'YYYY' as my last parameter the list box then contains 2006 five times, 2007 twelve times etc.
The second parameter in the applymap function seems to be my problem. I would like to put the field MonthYear in that parameter, because that is what I am trying to map on. However, I can't do that because MonthYear is a field that I am defining in the same load script. So, I tried to put the definition of MonthYear in that second parameter instead just like I did for my FiscalQuarter map, but it did not work because it created an entry for FiscalYear for every month as explained above.
I hope I explained that ok. If anyone has another strategy or something to put in that second parameter that they think might make this work i would love to hear it! Thanks a lot.
Scott
