Intervalmatch and COUNT in script

Wed, 20 Feb 2013 13:19:03 GMT

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

jagannalla — Wed, 20 Feb 2013 13:32:22 GMT

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

topic Intervalmatch and COUNT in script in QlikView

Intervalmatch and COUNT in script

Re: Intervalmatch and COUNT in script