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