Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Valid between date fields

Hi Dear QV Comunity

I Have this table

IdBEGDATEENDDATESTATUS
5001889701/01/201001/12/20102
5001889701/12/201031/05/20110
5001889701/06/201231/12/20122
5002128501/01/201031/05/20112
5002128501/06/201131/10/20120
5002128501/11/201231/12/20122
5008147801/01/201001/12/20102
5008147801/12/201031/05/20110
5008147801/06/201231/12/20122
5008322901/01/201031/05/20112
5008322901/06/201131/10/20120
5008322901/11/201231/12/20122
5301121001/01/201001/12/20102
5301121001/12/201031/05/20110
5301121001/06/201231/12/20122
5302858701/01/201031/05/20112
5302858701/06/201131/10/20120
5302858701/11/201231/12/20122
5304566601/01/201031/05/20112
5304566601/06/201131/10/20120
5304566601/11/201231/12/20122

An a Calendar table with dates between 01/01/2010 to 31/12/2012 bya by day

I need know the Quantity of ID With Status = 2 for each month. I trieda with the sample of cookbook (Counter Interval match) but the memory is out, Somebody can help me

1 Reply
msheraton
Contributor III

Re: Valid between date fields

See the script below.

Then create a chart with a dimenioon of:

=MonthName(Date)

and expresison of:

count({$<STATUS={'2'}>}DISTINCT Id)

Bit of a rush but hopefully is what you wanted.

Mark

data:

load * Inline [Id,          BEGDATE,          ENDDATE,          STATUS

50018897,          01/01/2010,          01/12/2010,          2

50018897,          01/12/2010,          31/05/2011,          0

50018897,          01/06/2012,          31/12/2012,          2

50021285,          01/01/2010,          31/05/2011,          2

50021285,          01/06/2011,          31/10/2012,          0

50021285,          01/11/2012,          31/12/2012,          2

50081478,          01/01/2010,          01/12/2010,          2

50081478,          01/12/2010,          31/05/2011,          0

50081478,          01/06/2012,          31/12/2012,          2

50083229,          01/01/2010,          31/05/2011,          2

50083229,          01/06/2011,          31/10/2012,          0

50083229,          01/11/2012,          31/12/2012,          2

53011210,          01/01/2010,          01/12/2010,          2

53011210,          01/12/2010,          31/05/2011,          0

53011210,          01/06/2012,          31/12/2012,          2

53028587,          01/01/2010,          31/05/2011,          2

53028587,          01/06/2011,          31/10/2012,          0

53028587,          01/11/2012,          31/12/2012,          2

53045666,          01/01/2010,          31/05/2011,          2

53045666,          01/06/2011,          31/10/2012,          0

53045666,          01/11/2012,          31/12/2012,          2

];

temp:

load min(BEGDATE) as minDate

,min(ENDDATE) as MaxDate

Resident data;

LET vDateMin = peek('minDate',0,'temp');

LET vDateMax = peek('MaxDate',0,'temp');

DROP TABle temp;

    

Calendar: 

load

Date($(vDateMin) + RowNo() - 1) AS Date

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

IntervalMatch:

IntervalMatch (Date) load DISTINCT BEGDATE , ENDDATE

Resident data;

          MAP_Date:

          Load

          (BEGDATE & '+' & ENDDATE)                                                                                           AS DateKey

          ,Date

          Resident IntervalMatch;

 

          drop table IntervalMatch;

 

 

          // 3.2 Join the indvidual dates back to the main table using the composite key

          ALL:

          NoConcatenate

          load *

          ,(BEGDATE & '+' & ENDDATE) as DateKey

          Resident data;

          INNER join (ALL)

                    load *

                    Resident MAP_Date;

 

          Drop tables MAP_Date, data;

          drop field DateKey;

Community Browser