Link several date fields from one table into Master Calendar

[#1]

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.

[Anonymous]

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..

[#3]

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.

[Anonymous]

Yes you are correct... on that sense...

I suppose the date field in the other table have all the dates...  5/10/2011, 5/7/2011, 5/15/2011, 6/01/2011 and 6/02/2011

So... all you need is to have a field in the Calendar table which has all the dates... and the other fields like Created, closed, due will have dates or flags for their particular dates... in this way... you can see created and closed as well..

Hope this helps...

thanks,

Aadil

[#5]

I am not sure I understand.

Would you mean like in the calendar table create fields:

Completed, created, closed, due

then link those fields into the job table basically creating 4 links between the two tables?

[#6]

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

[#7]

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

[#8]

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;

[jim_chan]

only 1 question - where did the "TempDate as DAILY_Date," daily_date come from?

[jim_chan]

if is based on my script , my master calendar, start from

MinMax:

load

Min(Date(main_date)) as MinDate,

Max(Date(main_date)) as MaxDate

Resident LINK_TABLE;

LET vMinDate = Num(Peek('MinDate', 0 , 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0 , 'MinMax'));

LET vToday = $(vMaxDate);