Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
RedSky001
Partner - Creator III
Partner - Creator III

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;