Adding of missing date values

jozisvk11 — Thu, 23 Mar 2023 19:53:20 GMT

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.

Re: Adding of missing date values

justISO — Fri, 24 Mar 2023 07:57:01 GMT

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]');

topic Re: Adding of missing date values in App Development

Adding of missing date values

Re: Adding of missing date values