Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;