Add Missing Values

tchovanec — Fri, 19 Apr 2013 16:12:09 GMT

What I am trying to do is show a date by hour. The end result would look like the following. I also tried the example in the cookbook, but it does not work because I have way too much data. I have over 1 million rows that I need to do this for.

INTERVAL	TOTAL
0045 - 0145	38
0145 - 0245	35
0245 - 0345	26
0345 - 0445	19
0445 - 0545	0
0545 - 0645	0
0645 - 0745	0
0745 - 0845	18
0845 - 0945	27
0945 - 1045	29
1045 - 1145	35
1145 - 1245	32
1245 - 1345	40
1345 - 1445	38
1445 - 1545	37
1545 - 1645	43
1645 - 1745	53
1745 - 1845	61
1845 - 1945	63
1945 - 2045	57
2045 - 2145	61
2145 - 2245	54
2245 - 2345	43

The issue I am running into is that when I have 0 it is not showing the interval. I also can't uncheck supress 0 values because then it shows everything and not just the dates I have chosen. Here is what I have so far. This gets me everything I want except for the 0 values are not showing.

TEMP_MIN_MAX_DATES:LOAD
     MIN(CONVERTED_ADT_ARRIVAL_TIME) as MIN_ADT_ARRIVAL_TIME,
     MAX(CONVERTED_ADT_ARRIVAL_TIME) as MAX_ADT_ARRIVAL_TIMERESIDENT ED_EVENT_INFO;
LET vMinDate=peek('MIN_ADT_ARRIVAL_TIME',-1,'TEMP_MIN_MAX_DATES');LET vMaxDate=peek('MAX_ADT_ARRIVAL_TIME',-1,'TEMP_MIN_MAX_DATES');DROP TABLE TEMP_MIN_MAX_DATES;


TempCalendar:LOAD//TimeStamp($(vMinDate) -(1395/1440) + ((RecNo()-1)/24) + (IterNo() -1)) AS STARTTimeStamp($(vMinDate) + 3/96  + ((RecNo()-1)/24) + (IterNo() -1)) AS StartDTTM
,TimeStamp(($(vMinDate) + 3/96  + ((RecNo()-1)/24) + (IterNo() -1)) + (1/24)) AS EndDTTMAutoGenerate 24 WHILE NUM($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)) + 3/96 ;

IslandCalendar:load
     StartDTTM
     ,EndDTTM
     ,MONTH(FLOOR(StartDTTM)) AS StartMonth
     ,YEAR(FLOOR(StartDTTM)) AS StartYear
     ,DATE(FLOOR(StartDTTM)) AS StartDt
     ,TIME(FRAC(StartDTTM)) AS StartTime
     ,TIME(FRAC(EndDTTM)) AS EndTimeRESIDENT TempCalendar
;
DROP TABLE TempCalendar;

This is the expression I am using in the chart.


=
SUM(if(DTTM_PAT_ARR <= EndDTTM AND DTTM_PAT_ROOMED >= EndDTTM,COUNTER_CSN
))

Thanks for any help.

topic Add Missing Values in QlikView

Add Missing Values