Missing Hours in chart

[jpando]

Hi team.

As you can see in the image attached I have a table with some hours where we track the repairs done by hour.

The need is to add/create the missing hours, because I want to show  "0" values, so we can see what are the hours with no repairs.

For example in the table above, on the 1st dimension I don't have "25-nov. 06am" and "25-nov 11am" either. I need to add those values in that table, those values are not in the data model. 

The values are updating every half an hour so the solution should be functional for the future values that are going to be appearing.

In the script I have this for the repair timestamp showed:

Repairs:
LOAD 
closed,
open,
unit_status,
last_unit_repair_timestamp
FROM

.........................................

and

MasterCalendarRepairs:

LOAD last_unit_repair_timestamp,
Day(last_unit_repair_timestamp)&'-'& Month(last_unit_repair_timestamp)&' '&time(last_unit_repair_timestamp,'hh tt') as DayMonthTimeRepair

Resident Repairs Order by last_unit_repair_timestamp ASC;

Many thanks in advance.

[edwin · Accepted solution]

you need to generate your calendar independent of the Fact so you have a comprehensive calendar with all the dates/times you need.

for example if you know when your data starts (assuming 1/1/2019)

load Timestamp( date('1/1/2019')+iterno()/24/2) as time
while floor(date(date('1/1/2019')+iterno()/24/2))<=date('$(vmaxDate)');
load 1 AutoGenerate(1);

where vmaxdate is a variable containing the max of your date time stamp;