Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ichimiike
New 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
jgd
Valued Contributor II

Re: Linking multiple dates from a single table into Master Calendar

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;

5 Replies
MVP
MVP

Re: Linking multiple dates from a single table into Master Calendar

Look here  -> Canonical Date

jgd
Valued Contributor II

Re: Linking multiple dates from a single table into Master Calendar

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
New Contributor III

Re: Linking multiple dates from a single table into Master Calendar

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

Re: Linking multiple dates from a single table into Master Calendar

please provide a small sample of your table to demonstrate.

thanks

regards

Marco

reddys310
Honored Contributor II

Re: Linking multiple dates from a single table into Master Calendar

Hi Mat,

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