9 Replies Latest reply: Nov 25, 2015 2:52 AM by Jim Chan RSS

    Link several date fields from one table into Master Calendar

      I have a Master Calendar loaded table.

       

      I have a Job Table that includes:

       

      JobCreatedate.

      JobDueDate

      JobRequiredbyDate

      JobCompletedDate

      JobClosedDate

       

       

      Is there any way to link these two together so that when a Date is selected on the master calendar, it shows what jobs where created, due, reuiredby completed and closed on that day? Kind of Like an overview of what happened.

       

      I understand that I could resident load the job table and relink it to the calander for each date field, but that seems a bit cumbersum if I want to show job details for each section, especially when I have several other tables linked into the job table.

        • Link several date fields from one table into Master Calendar
          Aadil M

          i suggest you have one date field in the calendar table which will have all the dates your Master calendar table field has so that it can be linked and any selection made would take effect...

           

          hope that helps..

            • Re: Link several date fields from one table into Master Calendar

              I understand that it would effect the selection in the table.

               

              Right now if I link one of the date fields in, like example createdate,  if I select a date it will show all jobs created that day, and show only when those specific jobs are due, closed etc.

               

              I was wondering if I could make it show when a date is selected all dates that happened on that day. I am assuming now that this can not be done without linking the other fields in a separate load statement.

               

              Example:

               

              Job, created, closed, due

              1, 5/7/2011, 5/15/2011, 5/10/2011

              2, 5/15/2011, 6/01/2011, 6/02/2011

               

              If the calendar date 5/15/2011 is selected I want to see job 1 since it was closed then, and also see job 2 that was created then.

               

              Right now if I link create date only I would only see job 2.

            • Re: Link several date fields from one table into Master Calendar

              Got the exact same problem, was there ever any sollution found to this?

                • Re: Link several date fields from one table into Master Calendar

                  Hi marcsliving,

                   

                  I have a solution regarding this. try this->

                  *******************************************

                  JobTable:

                  LOAD *

                  from JobTable;

                   

                  CONCATENATE

                   

                  LOAD *,

                            JobCreatedate AS DATE,

                            Job AS JobCreated

                  RESIDENT JobTable;

                   

                  CONCATENATE

                   

                  LOAD *,

                            JobDuedate AS DATE,

                            Job AS JobDue

                  RESIDENT JobTable;

                   

                  CONCATENATE

                   

                  LOAD *,

                            JobRequiredbyDate AS DATE,

                           Job AS JobRequired

                  RESIDENT JobTable;

                   

                  CONCATENATE

                   

                  LOAD *,

                            JobCompletedDate AS DATE,

                           Job AS JobCompleted

                  RESIDENT JobTable;

                   

                  Calendar:

                  LOAD DATE

                  from Calendar;

                  *************************************************************

                  when you select DATE from Calendar you will get count(JobCompleted),count(JobDue),count(JobRequired),count(JobCreated) etc regarding this DATE.

                   

                  Regards

                  Apurva

                    • Re: Link several date fields from one table into Master Calendar
                      Rahul Lakhina

                      Try the folloing script replace date with date fields in various table and table 1 to table 3 with the name of the table.

                       

                        /*Master Callender prepared based on all dates avaliable*/

                       

                      LET varMinDate = Num(rangemin(Peek(‘Date’, 0,'Table1'),Peek(‘Date’, 0,' Table2'),Peek(‘Date’, 0,' Table3')));

                       

                      LET varMaxDate = Num(rangemax(Peek(‘Date’, -1,' Table1),Peek(‘Date’, -1,' Table2'),Peek(‘Date’, -1,' Table3')));

                       

                      LET vToday = num(today());

                       

                       

                      //************************Temporary Calendar***************************

                       

                      //Create all days in the range from varMinDate to varMaxDate

                       

                       

                      TempCalendar:

                       

                      LOAD

                       

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

                       

                      Date($(varMinDate)+Iterno()-1) AS TempDate

                       

                      AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());

                       

                       

                      //**************************Master Calendar******************************

                       

                      //Building the master calendar with most date dimensions

                       

                       

                      MasterCalendar:

                       

                       

                      load

                       

                      TempDate as DAILY_Date,

                      week(TempDate) as Week,

                      Year(TempDate) as Year,

                      Month(TempDate) as Month,

                      Hour (TempDate) as Hour,

                      Day(TempDate) as Day,

                      Year2date(TempDate)*-1 as CurYTDFlag,

                      Year2date(TempDate,-1)*-1 as LastYTDFlag,

                      Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

                      Week(TempDate)& '-'&Year(TempDate) as WeekYear,

                      date(Today()-1) as Yesterday,

                      Week (today()) as CurrentWeek,

                      (Week (today()))-1 as LastWeek,

                      Weekday(TempDate) as WeekDay

                       

                      resident TempCalendar

                      order by TempDate Asc;

                       

                      Drop Table TempCalendar;