Linking multiple dates from a single table into Master Calendar

[ichimiike]

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

[sunny_talwar]

Look here  -> Canonical Date

[Josh_Good · Accepted solution]

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]

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]

Hi Mat,

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