5 Replies Latest reply: Dec 17, 2015 4:36 PM by Sangram Reddy RSS

    Linking multiple dates from a single table into Master Calendar

    Mat Smith

      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

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

          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;

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

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

              please provide a small sample of your table to demonstrate.

               

              thanks

               

              regards

               

              Marco

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

                Hi Mat,

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