Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I would recomend you having only one Calendar (disconected) and use Set Analysis to 'link' to your calendar at object level
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;