Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

Adding of missing date values

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.

 

Labels (3)
1 Solution

Accepted Solutions
justISO
Specialist
Specialist

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

View solution in original post

1 Reply
justISO
Specialist
Specialist

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