Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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? 

1 Reply
jagannalla
Not applicable

Re: Intervalmatch and COUNT in script

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