Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Frank_Hartmann
Honored Contributor II

how to connect date string to mastercalendar?

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
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

1 Solution

Accepted Solutions
Partner
Partner

Re: how to connect date string to mastercalendar?

In your data load you can use

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

View solution in original post

5 Replies
Digvijay_Singh
Honored Contributor III

Re: how to connect date string to mastercalendar?

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.

rgvavihs
Valued Contributor

Re: how to connect date string to mastercalendar?

Set your date variable

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

neetha_p
Honored Contributor

Re: how to connect date string to mastercalendar?

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;

Partner
Partner

Re: how to connect date string to mastercalendar?

In your data load you can use

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

View solution in original post

Re: how to connect date string to mastercalendar?

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