Adding of missing date values

[jozisvk11]

Good evening,

I am loading data from ERP SQL database but some days are not filled. I need to create Bar Chart with every day. 

Database is:

1.1.2023 - 500 hour

2.1.2023 - 600 hour

4.1.2023 - 550 hour

I need add day 3.1.2023 due to show every single day in Bar chart. So I need final database:

1.1.2023 - 500 hour

2.1.2023 - 600 hour

3.1. 2023 - null hour

4.1.2023 - 550 hour

Could you give an opportunity, how can I do that ??? 

Thank you.

Best Regards.

[justISO · Accepted solution]

Hi, I think the easiest way would be to generate a calendar with all the days and add them to the existing data, so in script level you can have something like this:

-- your SQL code --

LET vCalendarStart = Date('1.1.2023'); // Static start
LET vCalendarEnd = Date('4.1.2023');
LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

JOIN ([your_from_SQL_loaded_table_name])
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS Date //Change 'Date' to you SQL table date field name
AutoGenerate $(#vCalendarLength);

Notice, that calendar start and end dates I entered by hand, but it can be also taken from your from SQL loaded table additionally adding:

min_max:
LOAD
min(Date) as minDay,
max(Date) as maxDay
RESIDENT [your_from_SQL_loaded_table_name];

LET vCalendarStart = peek('minDay','[your_from_SQL_loaded_table_name]');
LET vCalendarEnd = peek('maxDay','[your_from_SQL_loaded_table_name]');