Valid between date fields

Mon, 19 Nov 2012 14:35:46 GMT

Hi Dear QV Comunity

I Have this table

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

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

Re: Valid between date fields

RedSky001 — Mon, 19 Nov 2012 17:42:52 GMT

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;

topic Valid between date fields in QlikView

Valid between date fields

Re: Valid between date fields