1 Reply Latest reply: Feb 20, 2013 8:32 AM by Jagan Nalla RSS

    Intervalmatch and COUNT in script

      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? 

        • Re: Intervalmatch and COUNT in script
          Jagan Nalla

          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