topic Re: how to connect date string to mastercalendar? in QlikView

how to connect date string to mastercalendar?

Frank_Hartmann — Wed, 02 Sep 2015 10:49:53 GMT

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

Re: how to connect date string to mastercalendar?

Digvijay_Singh — Wed, 02 Sep 2015 11:03:55 GMT

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.

Re: how to connect date string to mastercalendar?

Anonymous — Wed, 02 Sep 2015 11:04:22 GMT

Set your date variable

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

Re: how to connect date string to mastercalendar?

Anonymous — Wed, 02 Sep 2015 11:07:40 GMT

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;

Re: how to connect date string to mastercalendar?

stigchel — Wed, 02 Sep 2015 11:11:24 GMT

In your data load you can use

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

Re: how to connect date string to mastercalendar?

sunny_talwar — Wed, 02 Sep 2015 11:14:39 GMT

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