Ok....it's definitely text values, but I'm converting it to a date in the load script ...
My Master calendar looks like below with the MONTH field being the key field that I'm using.
Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(MONTH) as Year, // Your standard master calendar
Month(MONTH) as Month,
*;
Load Today() - MONTH as DaysAgo,
12*(Year(Today())-Year(MONTH)) + Month(Today()) - Month(MONTH) as MonthsAgo,
Today() - YearStart(Today(),0,$(vFM)) - MONTH + YearStart(MONTH,0,$(vFM)) as DaysAgoYTD,
Mod(Month(Today())-$(vFM),12) - Mod(Month(MONTH)-$(vFM),12) as MonthsAgoYTD,
*;
My concatenation looks like ....
Data_Rest:
Concatenate LOAD
FileName() as FILE,
@1 as FIN_YEAR,
@2 as COMPANY,
@3 as ACCOUNT,
@4 as CENTRE,
date(date#(@5),'DD MMM YYYY') as EFFECTIVE_DATE,
date(date#(@5),'DD MMM YYYY') as MONTH (***************this field********)
FROM
x:\filepath\ex*.csv
(txt, codepage is 1252, no labels, delimiter is ',', no quotes,no eof, filters(
Remove(Row, Pos(Top, 1))
))
where @2 = 2280
;
