Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
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.
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
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?
Got the exact same problem, was there ever any sollution found to this?
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
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;
only 1 question - where did the "TempDate as DAILY_Date," daily_date come from?
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);