Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ichimiike
Partner - Contributor III
Partner - Contributor III

Linking multiple dates from a single table into Master Calendar

Hi there,

I think I know the answer to this... but I figured I'd ask anyway.

I have a table which contains job details...  this table includes multiple dates which we may or may not want to use to measure by (ie Jobs created between... jobs cancelled between... jobs marked as finished production between...l jobs completed between etc).

In order for all these dates to relate back to a single master calendar, it would (if I'm thinking correctly) create a synthetic key which would include all these dates... but I have a feeling that it wouldn't perform correctly.

I suspect the way to get around this is to create multiple calendars... one for each date that would be included in the synthetic key.

Can anyone please advise if this IS the correct way to go about doing this or if there's a more elegant solution.

Thanks in advance for any advice you can offer.

Mat

1 Solution

Accepted Solutions
Josh_Good
Employee
Employee

Hi Mat,

You are correct that you will want to create multiple dates however you will not need (or want) to create synthetics keys between the calendars.

When I need to do this I use the script below. In the inline table you can list all the various calendars you need. Then the script will go through and create all the calendars you need.  You will need to modify the script to fit you needs.  Make sure you are familiar with the how to script a master calendar before trying to use this more advanced version.  Here is a link to a video on master calendars in general:Creating A Master Calendar

-Josh

Qlik

CalendarNames:

Load * Inline [

CalendarName, Table, CalendarNameSpaces, CalendarNameNoSpaces,

ActualEnd, Data, Actual End, Actual End

EstimatedEnd, Data, Estimated End, Estimated End

Start, Data, Start, Start,

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

For i = 0 to (NoOfRows('CalendarNames')-1);

Let varCalendarNameNoSpaces = Peek('CalendarName', $(i), 'CalendarNames');

Let varCalendarNameSpaces = Peek('CalendarNameSpaces', $(i), 'CalendarNames');

Let varCalendarFromTable = Peek('Table', $(i), 'CalendarNames');

Set varDateField = $(varCalendarNameSpaces) Date;

Temp:

Load

min([$(varDateField)]) as minDate,

max([$(varDateField)]) as maxDate

Resident $(varCalendarFromTable);

                Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

                TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                $(varCalendarNameNoSpaces)Calendar:

Load

TempDate AS [$(varCalendarNameSpaces) Date],

week(TempDate) As [$(varCalendarNameSpaces) Week],

Year(TempDate) As [$(varCalendarNameSpaces) Year],

Month(TempDate) As [$(varCalendarNameSpaces) Month],

Day(TempDate) As [$(varCalendarNameSpaces) Day],

//YeartoDate(TempDate)*-1 as CurYTDFlag,

//YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

// inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as [$(varCalendarNameSpaces) Month-Year],

ApplyMap('QuartersMap', month(TempDate), Null()) as [$(varCalendarNameSpaces) Quarter],

Week(TempDate) & '-' & Year(TempDate) as [$(varCalendarNameSpaces) Week-Year],

WeekDay(TempDate) as [$(varCalendarNameSpaces) Week-Day]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

NEXT;

Drop Table CalendarNames;

View solution in original post

5 Replies
sunny_talwar

Look here  -> Canonical Date

Josh_Good
Employee
Employee

Hi Mat,

You are correct that you will want to create multiple dates however you will not need (or want) to create synthetics keys between the calendars.

When I need to do this I use the script below. In the inline table you can list all the various calendars you need. Then the script will go through and create all the calendars you need.  You will need to modify the script to fit you needs.  Make sure you are familiar with the how to script a master calendar before trying to use this more advanced version.  Here is a link to a video on master calendars in general:Creating A Master Calendar

-Josh

Qlik

CalendarNames:

Load * Inline [

CalendarName, Table, CalendarNameSpaces, CalendarNameNoSpaces,

ActualEnd, Data, Actual End, Actual End

EstimatedEnd, Data, Estimated End, Estimated End

Start, Data, Start, Start,

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

For i = 0 to (NoOfRows('CalendarNames')-1);

Let varCalendarNameNoSpaces = Peek('CalendarName', $(i), 'CalendarNames');

Let varCalendarNameSpaces = Peek('CalendarNameSpaces', $(i), 'CalendarNames');

Let varCalendarFromTable = Peek('Table', $(i), 'CalendarNames');

Set varDateField = $(varCalendarNameSpaces) Date;

Temp:

Load

min([$(varDateField)]) as minDate,

max([$(varDateField)]) as maxDate

Resident $(varCalendarFromTable);

                Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

                TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                $(varCalendarNameNoSpaces)Calendar:

Load

TempDate AS [$(varCalendarNameSpaces) Date],

week(TempDate) As [$(varCalendarNameSpaces) Week],

Year(TempDate) As [$(varCalendarNameSpaces) Year],

Month(TempDate) As [$(varCalendarNameSpaces) Month],

Day(TempDate) As [$(varCalendarNameSpaces) Day],

//YeartoDate(TempDate)*-1 as CurYTDFlag,

//YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

// inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as [$(varCalendarNameSpaces) Month-Year],

ApplyMap('QuartersMap', month(TempDate), Null()) as [$(varCalendarNameSpaces) Quarter],

Week(TempDate) & '-' & Year(TempDate) as [$(varCalendarNameSpaces) Week-Year],

WeekDay(TempDate) as [$(varCalendarNameSpaces) Week-Day]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

NEXT;

Drop Table CalendarNames;

ichimiike
Partner - Contributor III
Partner - Contributor III
Author

Thanks to both of you...  It's good to know I was on the right track with multiple calendars... but even better to know they can be combined (sort of) if needed.

Cheers again,

Mat

MarcoWedel

please provide a small sample of your table to demonstrate.

thanks

regards

Marco

reddy-s
Master II
Master II

Hi Mat,

Making use of a Cononical Date as Sunny T mentioned is the way to go!