Can i join a Date Field to Three Date Fields

[#1]

Hi,

Can i join a Date field(Date field from Calendar Table) to three different Date fields in Same table.

Ex:

1.) Date Field from Calendar

2.) Raised Date, Pickedup Date and Closed Date from Service table.

Scenario: The scenario i am looking is if i select a date in my Calendar table

i should get information of services created that day, pickedup that day and closed that day.

Thanks,

Chris

[Clever_Anjos]

I would recomend you having only one Calendar (disconected) and use Set Analysis to 'link' to your calendar at object level

[Josh_Good]

No you can't.  Using set analysis is one possibility but is will make your expression complex.  I would recommend creating separate calendars for each date.

Once you have done that if you want to select on all three calendars with one click you can use an action.  The action would make the data change on other calendars when the date is change on one calendar.

Here is a script to load multiple calendars:

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;