how to connect date string to mastercalendar?

[Frank_Hartmann]

Hi QV Community,

is anybody able to explain me how to connect a string (e.g."august-2015") to my mastercalendar?

do i have to create a mapping table in order to connect the string to the corresponding month values or is there an easier way?

my master calendar looks like:

TempCalendar: 
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

MasterCalendar: 
LOAD

(year(TempDate)*12 + Month(TempDate)) - (year($(vDateMin))*12 + Month($(vDateMin))) as MonthNumber,
(year(TempDate)*12 + Month(TempDate)) - (year($(vDateToday))*12 + Month($(vDateToday))) as MonthDiff,
'AK_' & Date(TempDate,'YYYYMMDD') AS Referal_Date, 
Day(TempDate) AS CalendarDay, 
WeekDay(TempDate) AS CalendarWeekDay, 
Week(TempDate) AS CalendarWeek, 
Month(TempDate) AS CalendarMonth, 
Num(Month(TempDate),00) AS MonthAsNumber,
Year(TempDate) AS CalendarYear, 
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekDayAndYear,
Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear, 
Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,
Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,
Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,
Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CalendarCurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS CalendarLastYTDFlag,
if(WeekDay(TempDate)='So' and Day(TempDate)<='07','FirstMonthSunday','') as FirstMonthSunday
RESIDENT TempCalendar ORDER BY TempDate ASC; 

thank you for helping

[Digvijay_Singh]

Not sure but you may use Dual(String, Numeric) to connect through numeric field and display string value. In any case the linking has to happen through numeric value as QV internally do all date calculations using numeric value only. Thanks.

[Anonymous]

Set your date variable

varMinDate = Date(Date#('31/07/2015', 'DD/MM/YYYY'))

[Anonymous]

hi frank,

Please try as below:

Temp: //Extract min and max date using the date from fact table

LOAD Min(Date) as minDate,

          Today() as maxDate // Max(Date)

Resident FactTable;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

(year(TempDate)*12 + Month(TempDate)) - (year($(vDateMin))*12 + Month($(vDateMin))) as MonthNumber,

(year(TempDate)*12 + Month(TempDate)) - (year($(vDateToday))*12 + Month($(vDateToday))) as MonthDiff,

'AK_' & Date(TempDate,'YYYYMMDD') AS Referal_Date,

Day(TempDate) AS CalendarDay,

WeekDay(TempDate) AS CalendarWeekDay,

Week(TempDate) AS CalendarWeek,

Month(TempDate) AS CalendarMonth,

Num(Month(TempDate),00) AS MonthAsNumber,

Year(TempDate) AS CalendarYear,

Dual(Month(TempDate) & '-' & Year(TempDate)) AS CalendarMonthYear,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekDayAndYear,

Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear,

Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,

Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,

Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,

Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CalendarCurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS CalendarLastYTDFlag,

if(WeekDay(TempDate)='So' and Day(TempDate)<='07','FirstMonthSunday','') as FirstMonthSunday

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP Table TempCalendar;

[stigchel · Accepted solution]

In your data load you can use

month(Date#('august-2015','MMM-YYYY')) as CalendarMonth

[sunny_talwar]

Can you share how the string (august-2015) is getting created in your fact table? May be change it's format like this:

MonthName(Date#(Capitalize(DateField), 'MMMM-YYYY')) as DateField

and then connect this to mastercalendar on the field created as follows:

MonthName(TempDate) as DateField

HTH

Best,

Sunny