Intervalmatch and COUNT in script

[#1]

Hi,

I have the following script:

LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today()); LET varYear = year(today());LET varMonth= Month(today());
 
TempCalendar: LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
 
MasterCalendar: LOAD
    TempDate AS CalendarDate,
    Day(TempDate) AS CalendarDay,
    WeekDay(TempDate) AS CalendarWeekDay,
    Week(TempDate) AS CalendarWeek,
    Month(TempDate) AS CalendarMonth,
    Year(TempDate) AS CalendarYear,
    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC;
  DROP TABLE TempCalendar;
  LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());LET varYear = Year(today());LET varMonth= Month(today());SET vCY = Year(Today());

TempSickData:
LOAD ID_PER,

     ID_ZKT,

     date(DATIZIEK,'DD-MM-YYYY') as DATIZIEK,

     date(DATEZIEK,'DD-MM-YYYY') as DATEZIEK
    
;
SQL SELECT *
FROM PRODPIM."PIM_TZKT";

SickData:
noconcatenate load * Resident MasterCalendar;inner join(SickData) IntervalMatch(CalendarDate) load DATIZIEK, DATEZIEK Resident TempSickData; inner join load * Resident TempSickData;
drop table TempSickData;

Im doing an intervalmatch withe the two date fields (DATIZIEK and DATEZIEK) and my mastercalendar. When i click on a value from ID_PER (the person key) multiple fields in the CalendarDate column are selected. I want add a colum that counts these fields.

Can i do this in my script? 

[jagannalla]

Why you need so much logic for this.

Try this :

LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today()); LET varYear = year(today());LET varMonth= Month(today());

TempCalendar: LOAD

  $(vDateMin) + RowNo() - 1 AS DateNumber,

  Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar: LOAD

    TempDate AS CalendarDate,

    Day(TempDate) AS CalendarDay,

    WeekDay(TempDate) AS CalendarWeekDay,

    Week(TempDate) AS CalendarWeek,

    Month(TempDate) AS CalendarMonth,

    Year(TempDate) AS CalendarYear,

    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

  LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());LET varYear = Year(today());LET varMonth= Month(today());SET vCY = Year(Today());

SickData:

LOAD ID_PER,

     ID_ZKT,

     date(DATIZIEK,'DD-MM-YYYY') as DATIZIEK,

     date(DATEZIEK,'DD-MM-YYYY') as DATEZIEK

;

SQL SELECT *

FROM PRODPIM."PIM_TZKT";

Left Join(SickData)

IntervalMatch(CalendarDate) load DATIZIEK, DATEZIEK Resident SickData;

- Now it will link calender table with calendarDate.

- If you add your dim as ID_ZKT & expr as count(CalendarDate), you can see your result.

Hope it helps you.

Cheers!!

Jagan